Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't figure out how to open each file
I'm working on a macro to open a series of files in the same directory,
copy the page, then paste each page into a corresponding sheet with a corresponding name in a different workbook. 'Change to appropriate method and file Set wbPIP = Workbooks("PIP Test1.xls") frmYrQtrInput.Show sDirMod = "H:\AllDocs\CFSR PIP DD\SFY " & sCurYr & Space(1) & _ "Q" & sCurQtr & "\Records Mod\" sFName = Dir(sDirMod) lFileCt = 0 Do Until sFName = "" Set wb = Workbooks(sFName) <-----SUBSCRIPT OUT OF RANGE wb(sFName).Open Selection.CurrentRegion.Select Selection.Copy sShtName = Left(sFName, Len(sFName) - 7) sShtName = sShtName & "Records" wbPIP.Activate wbPIP.Worksheets(sShtName).Activate 'Range("b2").Select 'TEST Selection.CurrentRegion.Select Selection.Clear Range("A1").Select Selection.Paste lFileCt = lFileCt + 1 sFName = Dir() Loop MsgBox (lFileCt & " files were copied to the PIP spreadsheet."), vbOKOnly Application.ScreenUpdating = True End Sub Help! And thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't figure out how to open each file
you have to open it first:
Do Until sFName = "" Set wb = Workbooks.Open(sDirMod & sFName) -- Regards, Tom Ogilvy "davegb" wrote: I'm working on a macro to open a series of files in the same directory, copy the page, then paste each page into a corresponding sheet with a corresponding name in a different workbook. 'Change to appropriate method and file Set wbPIP = Workbooks("PIP Test1.xls") frmYrQtrInput.Show sDirMod = "H:\AllDocs\CFSR PIP DD\SFY " & sCurYr & Space(1) & _ "Q" & sCurQtr & "\Records Mod\" sFName = Dir(sDirMod) lFileCt = 0 Do Until sFName = "" Set wb = Workbooks(sFName) <-----SUBSCRIPT OUT OF RANGE wb(sFName).Open Selection.CurrentRegion.Select Selection.Copy sShtName = Left(sFName, Len(sFName) - 7) sShtName = sShtName & "Records" wbPIP.Activate wbPIP.Worksheets(sShtName).Activate 'Range("b2").Select 'TEST Selection.CurrentRegion.Select Selection.Clear Range("A1").Select Selection.Paste lFileCt = lFileCt + 1 sFName = Dir() Loop MsgBox (lFileCt & " files were copied to the PIP spreadsheet."), vbOKOnly Application.ScreenUpdating = True End Sub Help! And thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't figure out how to open each file
Tom Ogilvy wrote: you have to open it first: Do Until sFName = "" Set wb = Workbooks.Open(sDirMod & sFName) -- Regards, Tom Ogilvy As always, thanks Tom! "davegb" wrote: I'm working on a macro to open a series of files in the same directory, copy the page, then paste each page into a corresponding sheet with a corresponding name in a different workbook. 'Change to appropriate method and file Set wbPIP = Workbooks("PIP Test1.xls") frmYrQtrInput.Show sDirMod = "H:\AllDocs\CFSR PIP DD\SFY " & sCurYr & Space(1) & _ "Q" & sCurQtr & "\Records Mod\" sFName = Dir(sDirMod) lFileCt = 0 Do Until sFName = "" Set wb = Workbooks(sFName) <-----SUBSCRIPT OUT OF RANGE wb(sFName).Open Selection.CurrentRegion.Select Selection.Copy sShtName = Left(sFName, Len(sFName) - 7) sShtName = sShtName & "Records" wbPIP.Activate wbPIP.Worksheets(sShtName).Activate 'Range("b2").Select 'TEST Selection.CurrentRegion.Select Selection.Clear Range("A1").Select Selection.Paste lFileCt = lFileCt + 1 sFName = Dir() Loop MsgBox (lFileCt & " files were copied to the PIP spreadsheet."), vbOKOnly Application.ScreenUpdating = True End Sub Help! And thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i maintain same figure with formula link to another file | Excel Discussion (Misc queries) | |||
In Excel - Use Windows Explorer instead of File Open to open file | Excel Discussion (Misc queries) | |||
Code to Save As, then Open - can't figure it out! | Excel Discussion (Misc queries) | |||
Open a file do a macro ( made) and open next succesive file | Excel Programming | |||
HOW TO CONVERT A FIGURE INTO TEXT IN EXCEL FILE. PLEASE LET US KN | Excel Discussion (Misc queries) |