S
sh0t2bts
Hi All,
I have around 20 Excel workbook that run Automatically, collect data, save it inanother workbook and close them selves.
This is great apart from sometimes "depanding on time of day" the will take 2-3 minutes to complete and other times they will take 10-20 minutes.
I though I would get round this by opening them osing another macro in another workbook, (you will see previous posts where you lot helped me, Thanks)
I first programmed the macro to open each workbook using the "Workbooks.Open(Filename:="File.xls") etc" but it was skipping files It would open them but not run the "Aut
pen" macro inside.
I figured that there was a problem with my code so I found this:-
Dim fspec As String, fpath As String
fpath = "s:\Tracking\Autorun" fspec = "*.xls" With Application.FileSearch
.NewSearch
.LookIn = fpath
.Filename = fspec
If .Execute > 0 Then
Dim i As Integer
For i = 1 To .FoundFiles.Count
'Do not re-open this workbook
If .FoundFiles(i) <> ThisWorkbook.Name Then
With Application
.Calculation = xlManual
.CalculateBeforeSave = False
End With
'Add open file to workbooks collection
Workbooks.Open(Filename:=.FoundFiles(i), Updatelinks:=0 _
).RunAutoMacros Which:=xlAutoOpen
Application.DisplayAlerts = False
ActiveWorkbook.Close
End If '.FoundFiles...
Next i 'Loop
End If
End With
This opens every *.xls file in the given folder and runs the "Autpen" macro, we it sort of does. it does open every *.xls file but it only runs the macro for every other file.
in each *.xls file i placed this code "for testing purpuses"
Sub Autpen()
msgbox "File XXX is open"
End Sub
My Theroy was right it only opened and ran the macro for every second file, it does open every file but does not run the macro's..
I have gotten round this by numbering all of my files
"1 first report.xls"
"12 Dummy File"
"13 Second Report"
"14 Dummy File"
Etc,Etc,
How Do I get round this without having to have loads of dummy files??
Cheers
Mark
I have around 20 Excel workbook that run Automatically, collect data, save it inanother workbook and close them selves.
This is great apart from sometimes "depanding on time of day" the will take 2-3 minutes to complete and other times they will take 10-20 minutes.
I though I would get round this by opening them osing another macro in another workbook, (you will see previous posts where you lot helped me, Thanks)
I first programmed the macro to open each workbook using the "Workbooks.Open(Filename:="File.xls") etc" but it was skipping files It would open them but not run the "Aut
pen" macro inside.I figured that there was a problem with my code so I found this:-
Dim fspec As String, fpath As String
fpath = "s:\Tracking\Autorun" fspec = "*.xls" With Application.FileSearch
.NewSearch
.LookIn = fpath
.Filename = fspec
If .Execute > 0 Then
Dim i As Integer
For i = 1 To .FoundFiles.Count
'Do not re-open this workbook
If .FoundFiles(i) <> ThisWorkbook.Name Then
With Application
.Calculation = xlManual
.CalculateBeforeSave = False
End With
'Add open file to workbooks collection
Workbooks.Open(Filename:=.FoundFiles(i), Updatelinks:=0 _
).RunAutoMacros Which:=xlAutoOpen
Application.DisplayAlerts = False
ActiveWorkbook.Close
End If '.FoundFiles...
Next i 'Loop
End If
End With
This opens every *.xls file in the given folder and runs the "Aut
pen" macro, we it sort of does. it does open every *.xls file but it only runs the macro for every other file.in each *.xls file i placed this code "for testing purpuses"
Sub Aut
pen()msgbox "File XXX is open"
End Sub
My Theroy was right it only opened and ran the macro for every second file, it does open every file but does not run the macro's..
I have gotten round this by numbering all of my files
"1 first report.xls"
"12 Dummy File"
"13 Second Report"
"14 Dummy File"
Etc,Etc,
How Do I get round this without having to have loads of dummy files??
Cheers
Mark