J
Jim@Tech
I have a workbook which contains 6 worksheets and each worksheet queries the
same Access databse to return a different set of data (e.g. sheet 1 shows
project risks, sheet 2 shows project issues etc).
After I've imported the data, I do a small about of reformatting (replacing
1 and 0 with "Yes" and "No" and entering a formula into a column), I then
remove the queries to the database and save a copy of the workbook to a
shared folder.
I've come up with a macro to automate the reformatting and removal of the
queries from each sheet which works fine, but in order to post a 'clean' copy
of the workbook (i.e. no macros, links, queries etc) I then need to open the
VBA editor and remove the module which contains the macro.
I thought I'd be really clever and take the macro out of Module 1 and assign
it to a button on a new sheet in the workbook, so that once I'd run the macro
I can just delete the worksheet that contains the button and presto! - a
clean workbook.
Problem is, when I try to run the macro from the button, I get an error 400.
Can anyone tell me what causes an error 400 and what I need to do to fix it?
For reference, here's my code:
Sub reformat()
'
' Macro2 Macro
' Macro recorded 17/10/2008 by Jim Russell
'
Dim qt As QueryTable
'
Sheets("Risks").Select
Range("H3:H100").Select
Selection.FormulaR1C1 = _
"=IF(RC[-1]="""","""",IF(RC[-2]=""Remote"",1,IF(RC[-2]=""Infrequent"",3,IF(RC[-2]=""Likely"",5,IF(RC[-2]=""Very
Likely"",7,0))))*IF(RC[-1]=""Very
High"",4,IF(RC[-1]=""High"",3,IF(RC[-1]=""Medium"",2,IF(RC[-1]=""Low"",1,0)))))"
Columns("X:X").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select
Sheets("Issues").Select
Columns("M:N").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select
Sheets("Milestones").Select
Range("J:J,L:L").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select
Sheets("Dependencies").Select
Columns("I:L").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select
Sheets("Changes").Select
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select
Sheets("Summary").Select
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select
Dim FileName As Variant
FileName = Application.GetSaveAsFilename(filefilter:="Microsoft Office
Excel File (*.xls),*.xls")
If FileName = False Then Exit Sub
ActiveWorkbook.SaveAs FileName
End Sub
Thanks,
Jim.
same Access databse to return a different set of data (e.g. sheet 1 shows
project risks, sheet 2 shows project issues etc).
After I've imported the data, I do a small about of reformatting (replacing
1 and 0 with "Yes" and "No" and entering a formula into a column), I then
remove the queries to the database and save a copy of the workbook to a
shared folder.
I've come up with a macro to automate the reformatting and removal of the
queries from each sheet which works fine, but in order to post a 'clean' copy
of the workbook (i.e. no macros, links, queries etc) I then need to open the
VBA editor and remove the module which contains the macro.
I thought I'd be really clever and take the macro out of Module 1 and assign
it to a button on a new sheet in the workbook, so that once I'd run the macro
I can just delete the worksheet that contains the button and presto! - a
clean workbook.
Problem is, when I try to run the macro from the button, I get an error 400.
Can anyone tell me what causes an error 400 and what I need to do to fix it?
For reference, here's my code:
Sub reformat()
'
' Macro2 Macro
' Macro recorded 17/10/2008 by Jim Russell
'
Dim qt As QueryTable
'
Sheets("Risks").Select
Range("H3:H100").Select
Selection.FormulaR1C1 = _
"=IF(RC[-1]="""","""",IF(RC[-2]=""Remote"",1,IF(RC[-2]=""Infrequent"",3,IF(RC[-2]=""Likely"",5,IF(RC[-2]=""Very
Likely"",7,0))))*IF(RC[-1]=""Very
High"",4,IF(RC[-1]=""High"",3,IF(RC[-1]=""Medium"",2,IF(RC[-1]=""Low"",1,0)))))"
Columns("X:X").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select
Sheets("Issues").Select
Columns("M:N").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select
Sheets("Milestones").Select
Range("J:J,L:L").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select
Sheets("Dependencies").Select
Columns("I:L").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select
Sheets("Changes").Select
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select
Sheets("Summary").Select
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select
Dim FileName As Variant
FileName = Application.GetSaveAsFilename(filefilter:="Microsoft Office
Excel File (*.xls),*.xls")
If FileName = False Then Exit Sub
ActiveWorkbook.SaveAs FileName
End Sub
Thanks,
Jim.