CODEWITHSUNDEEP
excelvbavariables
Melvin ceylan
Melvin ceylan

Reputation: 29

Send data to another workbook via VBA

I've made this little macro to get the name of file from a folder, split it to get information from it and store them in variables that I send to another Excel file... And that's the part I'm struggling with ... I was abble to make it works the last friday but forget to save and I don't have backup. (Beginners issues) so I'm pretty sure I'm close to make it works again but I'm not finding why it doesn't work....

Can someone helps me pls ...

     
    Dim Chemin As String, Fichier As String, timeStamp As String
    Dim WB As Workbooks
    
    'Variable qui définit le fichier dans lequel le rapport est fait.
    'Set WB = Workbooks("EXCEL PATH WHERE I WRITE")
    
    'Définit le répertoire contenant les fichiers
    Chemin = "PATH WHERE THE FILES ARE STORE"
    
    'Variable qui récupère le nom des fichier peut importe l'extension.
    Fichier = Dir(Chemin & "*.*")
    
    'variable qui récupère le timestamp
    timeStamp = Split(Fichier, "_")(2)
    timeStamp = Split(timeStamp, ".")(0)

    'Boucle qui parse le nom de tous les fichiers
    Do While Len(Fichier) > 0
        'Variable pour aller à la derniere cellule de la la colonne
        emptyRowB  = Sheet.Range("B" & Rows.Count).End(xlUp).Row + 1
        
        
        'Remplissage des données
        Workbooks("MyRapportFile.xlsm").Worksheets("Sheet1").Cells(emptyRowB, 2).Value = timeStamp
        
        
    Loop

End Sub

Upvotes: 1

Views: 160

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

Note that for VBA it is not clear in which workbook emptyRowB = Sheets("HP")… is unless you specify it like you did in Workbooks("HP_Control-Copy.xlsm").Worksheets("HP").

Since you set your variable wb as that workbook

Set wb = Workbooks("HP_Control-Copy.xlsm")

you can use that

Dim emptyRowB As Long
emptyRowB = wb.Worksheets("HP").Range("B" & Rows.Count).End(xlUp).Row + 1

as well as here

wb.Worksheets("HP").Cells(emptyRowB, 2).Value = TimeStamp

In general you shoud never write Range or Cells without specifying in which worksheet and workbook that object is meant to be. Otherwise VBA assumes something because it cannot know for sure and it might assume something else than you and pick the wrong one. Don't let Excel guess!

Upvotes: 1

Related Questions