Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Anyone Know Workbook Openening & Saving Macro Alternatives?
Hi,
I have a user that is using a Macro in Exel 2002, that is opening and saving a number of files .xls files. He has about 50 of these subs, opening and saving a different file every time. Is there a better way of achieving the same thing without having to open & save all the documents, as you can imaging, opening & saving all of these documents can take a very long time. Sub Network1() ' ' Network1 Macro ' Macro recorded 05/01/2004 by Iain McLachlan ' ' Workbooks.Open Filename:= _ "\\<netwotk_path\<file_name.xls" Sheets("Data").Select Range("A1").Select Selection.QueryTable.Refresh BackgroundQuery:=False ActiveWorkbook.Save ActiveWindow.Close End Sub Sub Network2() ' ' Network2 Macro ' Macro recorded 05/01/2004 by Iain McLachlan ' ' Workbooks.Open Filename:= _ "\\<netwotk_path\<file_name.xls" Sheets("Data").Select Range("A1").Select Selection.QueryTable.Refresh BackgroundQuery:=False ActiveWorkbook.Save ActiveWindow.Close End Sub I did input the following into each sub: Application.ScreenUpdating = True Workbooks.Open Filename:= _ "\\<netwotk_path\<file_name.xls" Sheets("Data").Select Range("A1").Select Selection.QueryTable.Refresh BackgroundQuery:=False ActiveWorkbook.Save ActiveWindow.Close Application.ScreenUpdating = True This seems to have helped a bit, but the constant file opening & closing. Thanks for any help in advance, much appreciated |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Anyone Know Workbook Openening & Saving Macro Alternatives?
You may be able to adapt something from this. I have a worksheet with the
filepath in column A and the filename in column G. Sub Transfer() newPath = "Z:\TRANSFER TEST" For i = 2 To Cells(Rows.Count, "A").End(xlUp).row currentPath = Cells(i, "A").Value Filename = Cells(i, "G").Value Debug.Print currentPath, Filename, newPath, Filename 'copy currentPath & "\" & Filename As newPath & "\" & Filename FileCopy currentPath & "\" & Filename, newPath & "\" & Filename Next i End Sub HTH, Barb Reinhardt " wrote: Hi, I have a user that is using a Macro in Exel 2002, that is opening and saving a number of files .xls files. He has about 50 of these subs, opening and saving a different file every time. Is there a better way of achieving the same thing without having to open & save all the documents, as you can imaging, opening & saving all of these documents can take a very long time. Sub Network1() ' ' Network1 Macro ' Macro recorded 05/01/2004 by Iain McLachlan ' ' Workbooks.Open Filename:= _ "\\<netwotk_path\<file_name.xls" Sheets("Data").Select Range("A1").Select Selection.QueryTable.Refresh BackgroundQuery:=False ActiveWorkbook.Save ActiveWindow.Close End Sub Sub Network2() ' ' Network2 Macro ' Macro recorded 05/01/2004 by Iain McLachlan ' ' Workbooks.Open Filename:= _ "\\<netwotk_path\<file_name.xls" Sheets("Data").Select Range("A1").Select Selection.QueryTable.Refresh BackgroundQuery:=False ActiveWorkbook.Save ActiveWindow.Close End Sub I did input the following into each sub: Application.ScreenUpdating = True Workbooks.Open Filename:= _ "\\<netwotk_path\<file_name.xls" Sheets("Data").Select Range("A1").Select Selection.QueryTable.Refresh BackgroundQuery:=False ActiveWorkbook.Save ActiveWindow.Close Application.ScreenUpdating = True This seems to have helped a bit, but the constant file opening & closing. Thanks for any help in advance, much appreciated |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Anyone Know Workbook Openening & Saving Macro Alternatives?
On May 21, 2:59 pm, Barb Reinhardt
wrote: You may be able to adapt something from this. I have a worksheet with the filepath in column A and the filename in column G. Sub Transfer() newPath = "Z:\TRANSFER TEST" For i = 2 To Cells(Rows.Count, "A").End(xlUp).row currentPath = Cells(i, "A").Value Filename = Cells(i, "G").Value Debug.Print currentPath, Filename, newPath, Filename 'copy currentPath & "\" & Filename As newPath & "\" & Filename FileCopy currentPath & "\" & Filename, newPath & "\" & Filename Next i End Sub HTH, Barb Reinhardt Thanks for the suggestion Barb, I'll get the user to try editing all of that! :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
saving macro from workbook to Personal Macro Workbook | Excel Discussion (Misc queries) | |||
Saving a workbook step in a Macro | Excel Discussion (Misc queries) | |||
Saving Workbook via Macro | Excel Programming | |||
Updateing worksheets when openening workbook | Excel Programming | |||
Saving a workbook as PDF in Macro | Excel Programming |