Run-Time Error '1004'

[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

 

[Susan]

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

 

[KWarner]

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]

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.

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

 

[mcbarker]

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.

 

[Susan]

no wonder i was confused - i missed that line of your post.
apologies from a non-guru.
susan

 

[john]

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.

 

[mcbarker]

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.

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




__________ 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]

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.

 

[john]

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

 

[mcbarker]

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.

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.


--

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

 

[mcbarker]

John, thanks for taking the time to do this. I'll give this a try on Monday.
Again, thanks.

mcbarker

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




__________ 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]

I'd add some debug.print (or msgbox) lines to see what those values really are.

I'm still not convinced.

 

[mcbarker]

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.

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




__________ 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