opening file by a macro (in Excel2007)

W

Willem

Hi all,

I have a macro that creates a file with a pivot-table in it, and saves it
with a generated name based on the value of a specific cell.
The filename is stored in a global variable.
Next I run another macro that should open the file, but this causes an error
Run-time error 1004 " Method 'Open' of object 'Workbooks failed.

Below the relevant statements.

Public myName As Integer

Macro (1)
Range("F3").Select
myName = ActiveCell.Value

ActiveWorkbook.SaveAs Filename:="C:\Temp\" & myName & ".xlsm"

for opening the file this is the statement in a separate
Macro(2)
ChDir "C:\Temp"
Workbooks.Open Filename:="C:\Temp\" & myName

What also troubles me is that in Explorer I get another error message on
trying to open the file: "Excel cannot open the file [NAME] because the file
format or file extension is not valid. Verify that the file has not been
corrupted and that the file extension matches the format of the file".

Can anybody tell me what goes wrong?

Regards,

Willem
 
J

Joel

try these changes

from
ChDir "C:\Temp"
Workbooks.Open Filename:="C:\Temp\" & myName
to
Chdrive "C"
Folder = "C:\Temp\"
FName = dir(Folder & myName & ".xls*"
if FName = "" then
msgbox("Cannot find file : " & myName)
else
Workbooks.Open Filename:=Folder & FName
end if
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top