| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time Error '1004'
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-Time Error '1004'
i'm confused......... you said yourself that the laptop is drive c:,
and it works fine, because your code is specifying drive c: in the path. the computer is networked drive h: and it doesn't work, BECAUSE YOUR CODE SPECIFIES DRIVE C:. seems perfectly obvious to me................... if excel.version = XXXX (whatever version is on the laptop), use a path starting with c: elseif excel.version = XXXX (whatever version is on the computer), use a path starting with h: end if hope that helps :) susan On Mar 27, 1:12*pm, "mcbarker" wrote: 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-Time Error '1004'
If H: is a network drive, you will have to address it as such:
"\\users\departments\..." Of course you will substitute your own network path for the above. "mcbarker" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-Time Error '1004'
I also said that all macro references had been changed from drive C: to
drive H:, so the drive designations on the network computer are accurate. If it had been that simple, I wouldn't be asking here. Thanks anyway. "Susan" wrote in message ... i'm confused......... you said yourself that the laptop is drive c:, and it works fine, because your code is specifying drive c: in the path. the computer is networked drive h: and it doesn't work, BECAUSE YOUR CODE SPECIFIES DRIVE C:. seems perfectly obvious to me................... if excel.version = XXXX (whatever version is on the laptop), use a path starting with c: elseif excel.version = XXXX (whatever version is on the computer), use a path starting with h: end if hope that helps :) susan On Mar 27, 1:12 pm, "mcbarker" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-Time Error '1004'
That's been done. H: drive has been substituted for C: drive. All files
involved are designated correctly. Directories structures are accurate. Excel apparently just isn't finding the file (which is right where it is supposed to be) for some reason. I'm wondering if this is an Excel version issue. "KWarner" wrote in message ... If H: is a network drive, you will have to address it as such: "\\users\departments\..." Of course you will substitute your own network path for the above. "mcbarker" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-Time Error '1004'
no wonder i was confused - i missed that line of your post.
apologies from a non-guru. susan On Mar 27, 2:47*pm, "mcbarker" wrote: I also said that all macro references had been changed from drive C: to drive H:, so the drive designations on the network computer are accurate. If it had been that simple, I wouldn't be asking here. Thanks anyway. "Susan" wrote in message ... i'm confused......... you said yourself that the laptop is drive c:, and it works fine, because your code is specifying drive c: in the path. *the computer is networked drive h: and it doesn't work, BECAUSE YOUR CODE SPECIFIES DRIVE C:. seems perfectly obvious to me................... if excel.version = XXXX (whatever version is on the laptop), use a path starting with c: elseif excel.version = XXXX (whatever version is on the computer), use a path starting with h: end if hope that helps :) susan On Mar 27, 1:12 pm, "mcbarker" wrote: 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- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-Time Error '1004'
As a suggestion, why dont you try turning the macro recorder on then open
the file on your network manually? You can then compare the recorded result with your hard coded version. This may, or may not, give you some indication why your version fails. -- jb "mcbarker" wrote: 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-Time Error '1004'
The strange thing is that I use this routine in another macro in the same
workbook, which transfers data daily to an intermediate file, using an identical method, and it has been working perfectly for years. I'll try your suggestion when I go back to work on Monday. Thanks. "john" wrote in message ... As a suggestion, why don't you try turning the macro recorder on then open the file on your network manually? You can then compare the recorded result with your hard coded version. This may, or may not, give you some indication why your version fails. -- jb "mcbarker" wrote: 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 __________ Information from ESET NOD32 Antivirus, version of virus signature database 3970 (20090327) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 3970 (20090327) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-Time Error '1004'
I'd bet dollars to donuts that there's a difference in the spelling of the path
or filename. Or the values in the cells aren't what you expect them to be. Since the ranges aren't qualifed, maybe it's retrieving the value from the wrong cell. mcbarker wrote: 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 -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-Time Error '1004'
Dave Paterson has kindly highlighted what I was thinking of & suggested test
hopefully will show any hard coding errors if they exist. Other area of concern Dave rightly mentioned is that your VBA routine is not fully qualified which may be contributing to your problem. Before I set off home I had a quick play with your routine €“ I think I interpreted it correctly but if not, should give you some pointers how to ensure that your code behaves as intended under expected operating conditions. I also added error reporting to save the macro €œcrashing€ out if things go wrong. Sub SaveMonthly() Dim strMYear As String Dim strMMonth As String Dim strMExist As String Dim strMName As String Dim FName As String Dim MTwb As Workbook Dim MCDws As Worksheet FName = "C:\Quality Assurance\Chemistry History\Monthly Template.xls" On Error GoTo myerror Set MCDws = ThisWorkbook.Worksheets("Monthly Chemistry Data") ' ------------------------------- ' Select Chemistry Data worksheet ' ------------------------------- Application.StatusBar = "SAVING MONTHLY REPORT... PLEASE WAIT" ' ------------------------------------ ' Declare directory and file variables ' ------------------------------------ With MCDws strMMonth = Month(.Range("A4").Value) strMYear = Year(.Range("A4").Value) End With strMExist = Dir("C:\Quality Assurance\Chemistry Monthly Reports\" & _ strMYear & "\", vbDirectory) If strMExist = "" Then MkDir "C:\Quality Assurance\Chemistry Monthly Reports\" & strMYear & "\" End If 'create fullfilename strMName = "C:\Quality Assurance\Chemistry Monthly Reports\" & strMYear & _ "\Chemistry Monthly Report " & strMMonth & "-" & strMYear & ".xls" ' ---------------------------------------------------------------------------------- ' Copy Monthly Chemistry Data worksheet ' data to Monthly Template Workbook ' ---------------------------------------------------------------------------------- With MCDws .Range("A10", .Range("M10").End(xlDown)).Copy End With 'open the template Set MTwb = Workbooks.Open(Filename:=FName) 'paste data to template With MTwb .Worksheets("Monthly Chemistry Data").Range("A10").Paste ' Save the new monthly report .SaveCopyAs (strMName) 'close template .Close False End With With Application .CutCopyMode = False .StatusBar = False End With ' ---------------------------------------------------------- ' Clear data from old monthly report ' ---------------------------------------------------------- With MCDws .Range("A8").ClearContents .Range("A10", .Range("M10").End(xlDown)).ClearContents End With ' -------------------------------------------------- ' -------------------------------------------------- answer = MsgBox("The Chemistry Monthly Report has been saved to the file: " & Chr(10) & _ strMName, vbOKOnly) myerror: If Err 0 Then Msg = "Error # " & Str(Err.Number) & " was generated by " _ & Err.Source & Chr(13) & Err.Description MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext Err.Clear End If End Sub -- jb "mcbarker" wrote: The strange thing is that I use this routine in another macro in the same workbook, which transfers data daily to an intermediate file, using an identical method, and it has been working perfectly for years. I'll try your suggestion when I go back to work on Monday. Thanks. "john" wrote in message ... As a suggestion, why don't you try turning the macro recorder on then open the file on your network manually? You can then compare the recorded result with your hard coded version. This may, or may not, give you some indication why your version fails. -- jb "mcbarker" wrote: 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 __________ Information from ESET NOD32 Antivirus, version of virus signature database 3970 (20090327) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 3970 (20090327) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-Time Error '1004'
Nope... Transferring the macro was a straight cut and paste from the working
laptop file, then making sure that all of the C: drive references were changed to H: when the file was in place. This is one of those "drive you nuts" type of problems. I spent almost a day going over this bit by bit, even trying alternate code, but that line always stopped the macro dead in its tracks. "Dave Peterson" wrote in message ... I'd bet dollars to donuts that there's a difference in the spelling of the path or filename. Or the values in the cells aren't what you expect them to be. Since the ranges aren't qualifed, maybe it's retrieving the value from the wrong cell. mcbarker wrote: 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 -- Dave Peterson __________ Information from ESET NOD32 Antivirus, version of virus signature database 3970 (20090327) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 3970 (20090327) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-Time Error '1004'
John, thanks for taking the time to do this. I'll give this a try on Monday.
Again, thanks. mcbarker "john" wrote in message ... Dave Paterson has kindly highlighted what I was thinking of & suggested test hopefully will show any hard coding errors if they exist. Other area of concern Dave rightly mentioned is that your VBA routine is not fully qualified which may be contributing to your problem. Before I set off home I had a quick play with your routine - I think I interpreted it correctly but if not, should give you some pointers how to ensure that your code behaves as intended under expected operating conditions. I also added error reporting to save the macro "crashing" out if things go wrong. Sub SaveMonthly() Dim strMYear As String Dim strMMonth As String Dim strMExist As String Dim strMName As String Dim FName As String Dim MTwb As Workbook Dim MCDws As Worksheet FName = "C:\Quality Assurance\Chemistry History\Monthly Template.xls" On Error GoTo myerror Set MCDws = ThisWorkbook.Worksheets("Monthly Chemistry Data") ' ------------------------------- ' Select Chemistry Data worksheet ' ------------------------------- Application.StatusBar = "SAVING MONTHLY REPORT... PLEASE WAIT" ' ------------------------------------ ' Declare directory and file variables ' ------------------------------------ With MCDws strMMonth = Month(.Range("A4").Value) strMYear = Year(.Range("A4").Value) End With strMExist = Dir("C:\Quality Assurance\Chemistry Monthly Reports\" & _ strMYear & "\", vbDirectory) If strMExist = "" Then MkDir "C:\Quality Assurance\Chemistry Monthly Reports\" & strMYear & "\" End If 'create fullfilename strMName = "C:\Quality Assurance\Chemistry Monthly Reports\" & strMYear & _ "\Chemistry Monthly Report " & strMMonth & "-" & strMYear & ".xls" ' ---------------------------------------------------------------------------------- ' Copy Monthly Chemistry Data worksheet ' data to Monthly Template Workbook ' ---------------------------------------------------------------------------------- With MCDws .Range("A10", .Range("M10").End(xlDown)).Copy End With 'open the template Set MTwb = Workbooks.Open(Filename:=FName) 'paste data to template With MTwb .Worksheets("Monthly Chemistry Data").Range("A10").Paste ' Save the new monthly report .SaveCopyAs (strMName) 'close template .Close False End With With Application .CutCopyMode = False .StatusBar = False End With ' ---------------------------------------------------------- ' Clear data from old monthly report ' ---------------------------------------------------------- With MCDws .Range("A8").ClearContents .Range("A10", .Range("M10").End(xlDown)).ClearContents End With ' -------------------------------------------------- ' -------------------------------------------------- answer = MsgBox("The Chemistry Monthly Report has been saved to the file: " & Chr(10) & _ strMName, vbOKOnly) myerror: If Err 0 Then Msg = "Error # " & Str(Err.Number) & " was generated by " _ & Err.Source & Chr(13) & Err.Description MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext Err.Clear End If End Sub -- jb "mcbarker" wrote: The strange thing is that I use this routine in another macro in the same workbook, which transfers data daily to an intermediate file, using an identical method, and it has been working perfectly for years. I'll try your suggestion when I go back to work on Monday. Thanks. "john" wrote in message ... As a suggestion, why don't you try turning the macro recorder on then open the file on your network manually? You can then compare the recorded result with your hard coded version. This may, or may not, give you some indication why your version fails. -- jb "mcbarker" wrote: 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 __________ Information from ESET NOD32 Antivirus, version of virus signature database 3970 (20090327) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 3970 (20090327) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 3970 (20090327) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 3970 (20090327) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-Time Error '1004'
I'd add some debug.print (or msgbox) lines to see what those values really are.
I'm still not convinced. mcbarker wrote: Nope... Transferring the macro was a straight cut and paste from the working laptop file, then making sure that all of the C: drive references were changed to H: when the file was in place. This is one of those "drive you nuts" type of problems. I spent almost a day going over this bit by bit, even trying alternate code, but that line always stopped the macro dead in its tracks. "Dave Peterson" wrote in message ... I'd bet dollars to donuts that there's a difference in the spelling of the path or filename. Or the values in the cells aren't what you expect them to be. Since the ranges aren't qualifed, maybe it's retrieving the value from the wrong cell. mcbarker wrote: 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 -- Dave Peterson __________ Information from ESET NOD32 Antivirus, version of virus signature database 3970 (20090327) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 3970 (20090327) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run-Time Error '1004'
John, your code also generated errors, but I found a solution which works,
although I'm not sure why. As I initially mentioned, my macro worked using Excel 2000, but gave an error using Excel 2003, on the line: Workbooks.Open Filename:="C:\Quality Assurance\Chemistry History\Monthly Template.xls". It turns out that the code works perfectly if I eliminate the ".xls" part of the filename in that particular line. I would be interested, for reference, if anyone can tell me why this happens. Thanks to all who helped, especially John for taking the time to redo the code. Your assistance is greatly appreciated. "john" wrote in message ... Dave Paterson has kindly highlighted what I was thinking of & suggested test hopefully will show any hard coding errors if they exist. Other area of concern Dave rightly mentioned is that your VBA routine is not fully qualified which may be contributing to your problem. Before I set off home I had a quick play with your routine - I think I interpreted it correctly but if not, should give you some pointers how to ensure that your code behaves as intended under expected operating conditions. I also added error reporting to save the macro "crashing" out if things go wrong. Sub SaveMonthly() Dim strMYear As String Dim strMMonth As String Dim strMExist As String Dim strMName As String Dim FName As String Dim MTwb As Workbook Dim MCDws As Worksheet FName = "C:\Quality Assurance\Chemistry History\Monthly Template.xls" On Error GoTo myerror Set MCDws = ThisWorkbook.Worksheets("Monthly Chemistry Data") ' ------------------------------- ' Select Chemistry Data worksheet ' ------------------------------- Application.StatusBar = "SAVING MONTHLY REPORT... PLEASE WAIT" ' ------------------------------------ ' Declare directory and file variables ' ------------------------------------ With MCDws strMMonth = Month(.Range("A4").Value) strMYear = Year(.Range("A4").Value) End With strMExist = Dir("C:\Quality Assurance\Chemistry Monthly Reports\" & _ strMYear & "\", vbDirectory) If strMExist = "" Then MkDir "C:\Quality Assurance\Chemistry Monthly Reports\" & strMYear & "\" End If 'create fullfilename strMName = "C:\Quality Assurance\Chemistry Monthly Reports\" & strMYear & _ "\Chemistry Monthly Report " & strMMonth & "-" & strMYear & ".xls" ' ---------------------------------------------------------------------------------- ' Copy Monthly Chemistry Data worksheet ' data to Monthly Template Workbook ' ---------------------------------------------------------------------------------- With MCDws .Range("A10", .Range("M10").End(xlDown)).Copy End With 'open the template Set MTwb = Workbooks.Open(Filename:=FName) 'paste data to template With MTwb .Worksheets("Monthly Chemistry Data").Range("A10").Paste ' Save the new monthly report .SaveCopyAs (strMName) 'close template .Close False End With With Application .CutCopyMode = False .StatusBar = False End With ' ---------------------------------------------------------- ' Clear data from old monthly report ' ---------------------------------------------------------- With MCDws .Range("A8").ClearContents .Range("A10", .Range("M10").End(xlDown)).ClearContents End With ' -------------------------------------------------- ' -------------------------------------------------- answer = MsgBox("The Chemistry Monthly Report has been saved to the file: " & Chr(10) & _ strMName, vbOKOnly) myerror: If Err 0 Then Msg = "Error # " & Str(Err.Number) & " was generated by " _ & Err.Source & Chr(13) & Err.Description MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext Err.Clear End If End Sub -- jb "mcbarker" wrote: The strange thing is that I use this routine in another macro in the same workbook, which transfers data daily to an intermediate file, using an identical method, and it has been working perfectly for years. I'll try your suggestion when I go back to work on Monday. Thanks. "john" wrote in message ... As a suggestion, why don't you try turning the macro recorder on then open the file on your network manually? You can then compare the recorded result with your hard coded version. This may, or may not, give you some indication why your version fails. -- jb "mcbarker" wrote: 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 __________ Information from ESET NOD32 Antivirus, version of virus signature database 3970 (20090327) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 3970 (20090327) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 3970 (20090327) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 3981 (20090401) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
| Run time error 1004 Object defined error | Excel Programming | |||
| Run Time Error 1004: Application or Object Defined Error | Excel Programming | |||
| Run Time 1004 Error: Application or Object Difine Error | Excel Programming | |||
| run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Linear Mode
