jeffmcneese
New Member
- Joined
- Nov 11, 2011
- Messages
- 1
windows XP office 2007 saving as 2003 xls.
I have a Excel VBA which calls other excel spread sheets and run a VBA on opening. What started happening in the last couple of weeks is the spreadsheets will open but the macro does not start on ever other spreadsheet.
I can change orders of running in the master and still on every other one.
all of the VBA are identical in all the spread sheets. I have even experienced the spread sheet macro skipping and application.
Thanks Jeff McNeese
Has any one else experienced this?
Below is an example of my master. All was created my recording macros.
ChDir "M:\LAB\Kentucky Reports\Blank Kentucky Reports"
Workbooks.Open(Filename:= _
"M:\LAB\Kentucky Reports\Blank Kentucky Reports\BLANK BF AND QUALITY KENTUCKY Div DFA.xls" _
).RunAutoMacros Which:=xlAutoOpen
Workbooks.Open(Filename:= _
"M:\LAB\Kentucky Reports\Blank Kentucky Reports\BLANK BF AND QUALITY KENTUCKY Div 46.xls" _
, Origin:=xlWindows).RunAutoMacros Which:=xlAutoOpen
Workbooks.Open(Filename:= _
"M:\LAB\Kentucky Reports\Blank Kentucky Reports\BLANK BF AND QUALITY KENTUCKY Div 54.xls" _
, Origin:=xlWindows).RunAutoMacros Which:=xlAutoOpen
Workbooks.Open(Filename:= _
"M:\LAB\Kentucky Reports\Blank Kentucky Reports\BLANK BF AND QUALITY KENTUCKY Div 60.xls" _
, Origin:=xlWindows).RunAutoMacros Which:=xlAutoOpen
Workbooks.Open(Filename:= _
"M:\LAB\Kentucky Reports\Blank Kentucky Reports\BLANK BF AND QUALITY KENTUCKY Div 81.xls" _
, Origin:=xlWindows).RunAutoMacros Which:=xlAutoOpen
Workbooks.Open(Filename:= _
"M:\LAB\Kentucky Reports\Blank Kentucky Reports\BLANK BF AND QUALITY KENTUCKY Div 88.xls" _
, Origin:=xlWindows).RunAutoMacros Which:=xlAutoOpen
This is an example of the spreadsheet macro.
Sub Auto_open()
'
' Sheets("Form").Visible = True
' Sheets("FORM").Select
If Sheets("FORM").Range("G1").Text = "Done" Then Exit Sub
Application.Run "QueryTablesRefersh"
Application.Run "AllWorkBooksQueryDelete"
Application.Run "AllWorkbookPivotsRefresh"
Application.Run "Pastespecial"
Application.Run "BuildPath"
Application.Run "Save"
End Sub
Sub QueryTablesRefersh()
'Brings in Data from a file.
Dim pt As QueryTables
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each qtb In ws.QueryTables
qtb.Refresh BackgroundQuery:=False
Next qtb
Next ws
End Sub
Sub AllWorkBooksqueryDelete()
'Delete so no more updateing is possiable.
Dim qtb As QueryTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each qtb In ws.QueryTables
qtb.Delete
Next qtb
Next ws
End Sub
Sub AllWorkbookPivotsRefresh()
'brings Data to Pivot Tables.
Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
End Sub
Sub Pastespecial()
'to change date from formula to value
'Sheet1.Range("A1:A200") = Sheet1.Range("A1:A200").Value
Sheets("Form").Range("D1") = Sheets("Form").Range("D1").Value
Sheets("Form").Visible = False
End Sub
Sub BuildPath()
'This procedure will build a chain of sub directories
' Assume a Directory string is in cell A1 such as
'"C:\Category\Year" or "C:\Category\Year\Series"
Dim SubDirectories() As String
Dim i As Integer
Dim SubDirBuild As String
Dim fileSaveName As String
Filename = Sheets("form").Range("a8").Value
SubDirectories = Split(Sheets("form").Range("a7"), "\")
'If Directory specified then place at begining of string otherwise
'the current Directory will be used
If Right(SubDirectories(0), 1) = ":" Then SubDirBuild = SubDirectories(0)
'Add each subdirectory to string and try to make a subdirectory
For i = LBound(SubDirectories) + 1 To UBound(SubDirectories)
SubDirBuild = SubDirBuild & "\" & SubDirectories(i)
'MsgBox SubDirBuild
On Error Resume Next
MkDir SubDirBuild
Next i
End Sub
Sub SAVE()
Sheets("FORM").Range("G1") = "Done"
'ActiveWindow.SelectedSheets.Visible = False
Filename = Sheets("form").Range("A8").Value
SubDir = Sheets("form").Range("A7").Value
fileSaveName = SubDir & "\" & Filename
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=fileSaveName
Application.DisplayAlerts = True
End Sub
I have a Excel VBA which calls other excel spread sheets and run a VBA on opening. What started happening in the last couple of weeks is the spreadsheets will open but the macro does not start on ever other spreadsheet.
I can change orders of running in the master and still on every other one.
all of the VBA are identical in all the spread sheets. I have even experienced the spread sheet macro skipping and application.
Thanks Jeff McNeese
Has any one else experienced this?
Below is an example of my master. All was created my recording macros.
ChDir "M:\LAB\Kentucky Reports\Blank Kentucky Reports"
Workbooks.Open(Filename:= _
"M:\LAB\Kentucky Reports\Blank Kentucky Reports\BLANK BF AND QUALITY KENTUCKY Div DFA.xls" _
).RunAutoMacros Which:=xlAutoOpen
Workbooks.Open(Filename:= _
"M:\LAB\Kentucky Reports\Blank Kentucky Reports\BLANK BF AND QUALITY KENTUCKY Div 46.xls" _
, Origin:=xlWindows).RunAutoMacros Which:=xlAutoOpen
Workbooks.Open(Filename:= _
"M:\LAB\Kentucky Reports\Blank Kentucky Reports\BLANK BF AND QUALITY KENTUCKY Div 54.xls" _
, Origin:=xlWindows).RunAutoMacros Which:=xlAutoOpen
Workbooks.Open(Filename:= _
"M:\LAB\Kentucky Reports\Blank Kentucky Reports\BLANK BF AND QUALITY KENTUCKY Div 60.xls" _
, Origin:=xlWindows).RunAutoMacros Which:=xlAutoOpen
Workbooks.Open(Filename:= _
"M:\LAB\Kentucky Reports\Blank Kentucky Reports\BLANK BF AND QUALITY KENTUCKY Div 81.xls" _
, Origin:=xlWindows).RunAutoMacros Which:=xlAutoOpen
Workbooks.Open(Filename:= _
"M:\LAB\Kentucky Reports\Blank Kentucky Reports\BLANK BF AND QUALITY KENTUCKY Div 88.xls" _
, Origin:=xlWindows).RunAutoMacros Which:=xlAutoOpen
This is an example of the spreadsheet macro.
Sub Auto_open()
'
' Sheets("Form").Visible = True
' Sheets("FORM").Select
If Sheets("FORM").Range("G1").Text = "Done" Then Exit Sub
Application.Run "QueryTablesRefersh"
Application.Run "AllWorkBooksQueryDelete"
Application.Run "AllWorkbookPivotsRefresh"
Application.Run "Pastespecial"
Application.Run "BuildPath"
Application.Run "Save"
End Sub
Sub QueryTablesRefersh()
'Brings in Data from a file.
Dim pt As QueryTables
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each qtb In ws.QueryTables
qtb.Refresh BackgroundQuery:=False
Next qtb
Next ws
End Sub
Sub AllWorkBooksqueryDelete()
'Delete so no more updateing is possiable.
Dim qtb As QueryTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each qtb In ws.QueryTables
qtb.Delete
Next qtb
Next ws
End Sub
Sub AllWorkbookPivotsRefresh()
'brings Data to Pivot Tables.
Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
End Sub
Sub Pastespecial()
'to change date from formula to value
'Sheet1.Range("A1:A200") = Sheet1.Range("A1:A200").Value
Sheets("Form").Range("D1") = Sheets("Form").Range("D1").Value
Sheets("Form").Visible = False
End Sub
Sub BuildPath()
'This procedure will build a chain of sub directories
' Assume a Directory string is in cell A1 such as
'"C:\Category\Year" or "C:\Category\Year\Series"
Dim SubDirectories() As String
Dim i As Integer
Dim SubDirBuild As String
Dim fileSaveName As String
Filename = Sheets("form").Range("a8").Value
SubDirectories = Split(Sheets("form").Range("a7"), "\")
'If Directory specified then place at begining of string otherwise
'the current Directory will be used
If Right(SubDirectories(0), 1) = ":" Then SubDirBuild = SubDirectories(0)
'Add each subdirectory to string and try to make a subdirectory
For i = LBound(SubDirectories) + 1 To UBound(SubDirectories)
SubDirBuild = SubDirBuild & "\" & SubDirectories(i)
'MsgBox SubDirBuild
On Error Resume Next
MkDir SubDirBuild
Next i
End Sub
Sub SAVE()
Sheets("FORM").Range("G1") = "Done"
'ActiveWindow.SelectedSheets.Visible = False
Filename = Sheets("form").Range("A8").Value
SubDir = Sheets("form").Range("A7").Value
fileSaveName = SubDir & "\" & Filename
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=fileSaveName
Application.DisplayAlerts = True
End Sub