opening file by a macro (in Excel2007)

[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

 

[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