Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to open up 2 file in excel using macros
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to open up 2 file in excel using macros
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 -- Regards, Tom Ogilvy "LMI" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to open up 2 file in excel using macros
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 "LMI" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros - Open an access file from de Excel? | Excel Discussion (Misc queries) | |||
Open file with macros | Excel Discussion (Misc queries) | |||
Not allow to open file it do not accept macros | Excel Programming | |||
Open file without macros | Excel Programming | |||
In VBA, Open a file with macros disabled | Excel Programming |