Anyone Know Workbook Openening & Saving Macro Alternatives?

[darren.woolston]

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

 

[Barb Reinhardt]

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

 

[darren.woolston]

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!