How do I fix a Run-time error '1004' being produced by my macro?

0
votes

I've been battling with this macro on and off. I've got it to work and it worked just fine until all of a sudden when I got a Run-time error

"'Test.xlsm' could not be found. Check the spelling of the file name, and verify that the file location is correct.

If you are tyring to open the file from your list of most recently used files, make sure that the file has not been renamed, moved, or deleted. "

I did not rename or move the file from it's location yet I'm getting this error. I'm lost on why all of a sudden this occurs. 

Sub ImportData_Click()

' open the source workbook and select the source sheet
Workbooks.Open Filename:="Test.xlsm"
Sheets("Make").Select

' copy the source range
Sheets("Make").Range("A1:Z630").Select
Selection.Copy

' select current workbook and paste the values starting at A1
ThisWorkbook.Activate
Sheets("Make").Select
Sheets("Make").Range("A1:Z630").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

' close the source workbook
Windows("Test.xlsm").Activate
ActiveWorkbook.Close

End Sub
1
excelvba
You say you haven't moved the file you are trying to open but have you saved any other Excel files to a different folder? You haven't explicitly declared a path for the file, just the filename, so it is likely trying to find a file with the name "Test.xlsm" in whatever folder was the last folder you saved a file to. - Dave
Dave thanks for your input. You were right the file path was not declared and I couldn't figure that out. I realized this after I posted and should have edited this to ask how to declare the file path. - Joshua Buckley
Sorry, should have mentioned it in my comment! It might also be worth looking up the application.GetOpenFilename function if you are going to be doing similar things in future - Dave

1 Answers

0
votes

Try adding the full path, it has worked for me the last time I've tried.

Workbooks.Open Filename:="C:\Porn\Test.xlsm"