M
mcbarker
The macro shown below works perfectly on my laptop using Excel 2000 (used
for writing and testing macros before placing them on the network). When I
transfer it over to the computer that it's actually going to be used on,
which uses Excel 2003, the macro stops dead at the following line:
Workbooks.Open Filename:="C:\Quality Assurance\Chemistry History\Monthly
Template.xls"
The error looks like this:
Run-Time error '1004'
'filename' could not be found. Check the spelling of the filename, and
verify that the file location is correct.
If you are trying to open the file from your list of recently used files on
the file menu, make sure that the file has not been renamed, moved, or
deleted.
The only difference between the two computer systems (apart from the Excel
versions) is that the drive designation on the networked computer is H:
instead of C:. All C: references are changed to H: on the working computer.
All files are where they should be, and can be opened manually, or found
doing a Windows search. I can't figure this one out. Any help would be
appreciated. Thanks.
***********************
Sub SaveMonthly()
Dim strMYear As String, strMMonth As String, strMExist As String, strMName
As String
' -------------------------------
' Select Chemistry Data worksheet
' -------------------------------
Application.StatusBar = "SAVING MONTHLY REPORT... PLEASE WAIT"
Sheets("Chemistry Data").Select
' ------------------------------------
' Declare directory and file variables
' ------------------------------------
strMMonth = Month(Range("A4").Value)
strMYear = Year(Range("A4").Value)
strMExist = Dir("C:\Quality Assurance\Chemistry Monthly Reports\" & strMYear
& "\", vbDirectory)
If strMExist = "" Then
MkDir "C:\Quality Assurance\Chemistry Monthly Reports\" & strMYear & "\"
End If
strMName = "C:\Quality Assurance\Chemistry Monthly Reports\" & strMYear &
"\Chemistry Monthly Report " & strMMonth & "-" & strMYear & ".xls"
' ----------------------------------------------------------------------------------
' Select Monthly Chemistry Data worksheet and copy data to Monthly Template
workbook
' ----------------------------------------------------------------------------------
Sheets("Monthly Chemistry Data").Select
Range("A10").Select
Range("A10", Range("M10").End(xlDown)).Select
Selection.Copy
Workbooks.Open Filename:="C:\Quality Assurance\Chemistry History\Monthly
Template.xls"
Windows("Monthly Template.xls").Activate
Range("A10").Select
ActiveSheet.Paste
Range("A1").Select
ChDir "C:\Quality Assurance\Chemistry Monthly Reports\" & strMYear
ActiveWorkbook.SaveAs (strMName)
ActiveWorkbook.Close
ChDir "C:\Quality Assurance\Chemistry History\"
' ----------------------------------------------------------
' Clear data from old monthly report and template worksheets
' ----------------------------------------------------------
Range("A8").Select
Selection.ClearContents
Range("A10", Range("M10").End(xlDown)).Select
Selection.ClearContents
Workbooks.Open Filename:="C:\Quality Assurance\Chemistry History\Monthly
Template.xls"
Windows("Monthly Template.xls").Activate
Range("A8").Select
Selection.ClearContents
Range("A10", Range("M10").End(xlDown)).Select
Selection.ClearContents
' --------------------------------------------------
' Save new monthly report and cleared template files
' --------------------------------------------------
ActiveWorkbook.Save
ActiveWorkbook.Close
Sheets("Chemistry Data").Select
Range("A1").Select
answer = MsgBox("The Chemistry Monthly Report has been saved to the file: "
& strMName, vbOKOnly)
Application.StatusBar = False
End Sub
for writing and testing macros before placing them on the network). When I
transfer it over to the computer that it's actually going to be used on,
which uses Excel 2003, the macro stops dead at the following line:
Workbooks.Open Filename:="C:\Quality Assurance\Chemistry History\Monthly
Template.xls"
The error looks like this:
Run-Time error '1004'
'filename' could not be found. Check the spelling of the filename, and
verify that the file location is correct.
If you are trying to open the file from your list of recently used files on
the file menu, make sure that the file has not been renamed, moved, or
deleted.
The only difference between the two computer systems (apart from the Excel
versions) is that the drive designation on the networked computer is H:
instead of C:. All C: references are changed to H: on the working computer.
All files are where they should be, and can be opened manually, or found
doing a Windows search. I can't figure this one out. Any help would be
appreciated. Thanks.
***********************
Sub SaveMonthly()
Dim strMYear As String, strMMonth As String, strMExist As String, strMName
As String
' -------------------------------
' Select Chemistry Data worksheet
' -------------------------------
Application.StatusBar = "SAVING MONTHLY REPORT... PLEASE WAIT"
Sheets("Chemistry Data").Select
' ------------------------------------
' Declare directory and file variables
' ------------------------------------
strMMonth = Month(Range("A4").Value)
strMYear = Year(Range("A4").Value)
strMExist = Dir("C:\Quality Assurance\Chemistry Monthly Reports\" & strMYear
& "\", vbDirectory)
If strMExist = "" Then
MkDir "C:\Quality Assurance\Chemistry Monthly Reports\" & strMYear & "\"
End If
strMName = "C:\Quality Assurance\Chemistry Monthly Reports\" & strMYear &
"\Chemistry Monthly Report " & strMMonth & "-" & strMYear & ".xls"
' ----------------------------------------------------------------------------------
' Select Monthly Chemistry Data worksheet and copy data to Monthly Template
workbook
' ----------------------------------------------------------------------------------
Sheets("Monthly Chemistry Data").Select
Range("A10").Select
Range("A10", Range("M10").End(xlDown)).Select
Selection.Copy
Workbooks.Open Filename:="C:\Quality Assurance\Chemistry History\Monthly
Template.xls"
Windows("Monthly Template.xls").Activate
Range("A10").Select
ActiveSheet.Paste
Range("A1").Select
ChDir "C:\Quality Assurance\Chemistry Monthly Reports\" & strMYear
ActiveWorkbook.SaveAs (strMName)
ActiveWorkbook.Close
ChDir "C:\Quality Assurance\Chemistry History\"
' ----------------------------------------------------------
' Clear data from old monthly report and template worksheets
' ----------------------------------------------------------
Range("A8").Select
Selection.ClearContents
Range("A10", Range("M10").End(xlDown)).Select
Selection.ClearContents
Workbooks.Open Filename:="C:\Quality Assurance\Chemistry History\Monthly
Template.xls"
Windows("Monthly Template.xls").Activate
Range("A8").Select
Selection.ClearContents
Range("A10", Range("M10").End(xlDown)).Select
Selection.ClearContents
' --------------------------------------------------
' Save new monthly report and cleared template files
' --------------------------------------------------
ActiveWorkbook.Save
ActiveWorkbook.Close
Sheets("Chemistry Data").Select
Range("A1").Select
answer = MsgBox("The Chemistry Monthly Report has been saved to the file: "
& strMName, vbOKOnly)
Application.StatusBar = False
End Sub