How to open up 2 file in excel using macros

[LMI]

Below i have some code that will open up just a single excel file how can i
open up multiple file at a time?


Sub HB_ESG1()
Workbooks.OpenText FileName:= _
"P:\BIW_Qual\fits\BIW_FITS\HB_ESG1.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=True, _
Space:=False, Other:=False
Range("a1:cw20").Select
Selection.Copy
Windows("HB_ESG1.xls").Activate
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("Data ESG_HB").Select
Range("B3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("COMMANDS").Select
Windows("HB_ESG1.TXT").Activate
ActiveWindow.Close
End Sub

 

[Tom Ogilvy]

Think you would have to repeat the code or use a second procedure and pass
in the file names

Sub OpenBooks(sName as String)
Dim bk as Workbook
Workbooks.OpenText FileName:= _
sName,Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, _
Space:=False, Other:=False
set bk = Activeworkbook
Range("a1:cw20").Copy
Windows("HB_ESG1.xls").Activate
Sheets("Data ESG_HB").Select
Range("B3").Select
if not isempty(selection) then _
Range("B3").End(xldown)(2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("COMMANDS").Select
Bk.Close SaveChanges:=False
End Sub

Sub HB_ESG()
s ="P:\BIW_Qual\fits\BIW_FITS\HB_ESG1.TXT"
s1 = "P:\BIW_Qual\fits\BIW_FITS\HB_ESG2.TXT"
OpenBooks s
OpenBooks s1
End Sub

run HB_ESG to process 2 files

 

[John]

Hi there,

Try the code below, but with multiple files open you need to be even more
careful when you refer to the 'ActiveSheet/Workbook' that it's the file you
think it is!

Best regards

John


Sub HB_ESG1()

Dim wkb1 As Workbook
Dim wkb2 As Workbook
Dim sFileName1 As String
Dim sFileName2 As String

sFileName1 = "P:\BIW_Qual\fits\BIW_FITS\HB_ESG1.TXT"
sFileName2 = "SecondFile path and name.txt"

Set wkb1 = Workbooks.OpenText(Filename:=sFileName1, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=True, _
Space:=False, Other:=False)

Set wkb2 = Workbooks.OpenText(Filename:=sFileName2, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=True, _
Space:=False, Other:=False)

'Now you've got a reference to each
'workbook, you can use them directly
'For example:
wkb1.Worksheets(1).Range("A1:CW20").Select
'or
wkb1.Close