Refer to workbooks as workbook(1), workbook(2) etc.

[daniel22bg]

Hi,

I want to write a loop which goes through all excel files in a folder and copies some stuff.
For the purpose, I want to avoid using file references by any specific file name. However, I am not sure how I can do that formally in VBA. Can you please help? I need something of the kind:

For i = 1 to 100
Workbook(i).Sheets("SheetName").Select
....
Next i

 

[daverunt]

Hi Daniel,


I would normally use the following to go through a directory of files to extract infiormation from each. There is no reference to specific files.

'Display Open Dialog to select  file directory
  filenames = Application.GetOpenFilename("Excel Files (*.xls*)," & _
    "*.xls*", 1, "Select Files", "Open", False)
 
'If the user cancels file selection then exit
     If TypeName(filenames) = "Boolean" Then
        Exit Sub
     End If
 
'Set xls as SourceFile
        SourceFile = Dir("*.xls")
 
     Do While SourceFile <> ""
'Open the File
        Workbooks.Open SourceFile
        Set XLSFile = ActiveWorkbook
 
 
------------DO SOMETHING WITH THE ACTIVE WORKBOOK---------------
 
 
'Close open file
    Windows.Application.CutCopyMode = False
    XLSFile.Close False
 SourceFile = Dir
  Loop

 

[daniel22bg]

I got it, I was missing a slash:
"Workbooks.Open "C:\Data Files\" & objfile.Name "

 

[daniel22bg]

Hey, that's very usefull. Thanks a lot
Sorry for the last comment, they were for another post