CAN'T GET MACRO TO CALL UP A FILE AND OPEN IT.

[romij29]

Hello,

I have written a macro that copies arange of worksheets from one workbook to another, however there seems to be a problem with getting the workbook to open even though the pathway has been specified and everything seems to be alright. Not sure what the problem is,but I did delete the file a few days ago and replaced it with an updated version. The code is:
Public Sub CSV_Template()
Application.ScreenUpdating = False
intRow = 2
strPath = Workbooks("Budget_Macro.xls").Sheets("Budget").Range("E2")
strTemplate = "All Cinemas Phased.xls"
strCSV = "Cinemas CSV 2005.xls"
Application.StatusBar = "Opening Files"
Workbooks.Open Filename:=strPath & strTemplate (This is the line that gives me Error 1004)
Workbooks.Open Filename:=strPath & strCSV (However this line opens perfectly!)

Do Until Workbooks("Budget_Macro.xls").Sheets("Budget").Range("A" & intRow) = ""
strCinema = Workbooks("Budget_Macro.xls").Sheets("Budget").Range("A" & intRow)
strRate = Workbooks("Budget_Macro.xls").Sheets("Budget").Range("C" & intRow)
strCSA = Workbooks("Budget_Macro.xls").Sheets("Budget").Range("D" & intRow)
Application.StatusBar = "Creating Template " & strCinema

Workbooks(strTemplate).Sheets(strCinema).Range("D77") = strRate
Workbooks(strTemplate).Sheets(strCinema).Range("Q10") = strCSA
Workbooks(strTemplate).Sheets(strCinema).Range("Q20") = strCSA
'Create a template suitable for CSV file inclusive of cost calculations
' as well as numbers being values only
Workbooks(strTemplate).Sheets(strCinema).Cells.Copy

Workbooks(strCSV).Sheets(strCinema).Range("A1").PasteSpecial xlValues
Workbooks(strCSV).Sheets(strCinema).Range("D60:O66").Formula = "=D8-D18"
Workbooks(strCSV).Sheets(strCinema).Range("D60:O66").Copy
Workbooks(strCSV).Sheets(strCinema).Range("D18:O24").PasteSpecial xlValues

Workbooks(strCSV).Sheets(strCinema).Range("C60:O103") = 0
intRow = intRow + 1
Loop
Application.DisplayAlerts = False


Application.CutCopyMode = False
Application.StatusBar = False
End Sub

Thanks,

romij29

 

[strongm]

OK, 1004 can also mean an unrecognized file format - so is it possible that "All Cinemas Phased.xls" is not actually an Excel file? Or perhaps is corrupt?

 

[strongm]

One other point - this is the VB forum, not the VBA forum (forum707) ...

 

[romij29]

hello strongm,

Just checked that. The file is free of viruses and I used the immediate window to detect the file path and it was
P:\All Cinemas Phased.xls.
The file exists and I can open it but macro can't. Did check the fle path and everything. What i did earlier was delete file of same name and replace with an updated version.

 

[strongm]

OK, if you type

workbooks.open "P:\All Cinemas Phased.xls"

in the immediate window, and then press return, what happens?

 

[romij29]

Strongm,
Thanks for the reply
I get (in ditto):

P:\All Cinemas Phased.xls 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 most recently used files on the File menu,make sure that the file has not been renamed,deleted or moved.

romij29

 

[strongm]

Fine, so the file, as named, does not exist.

 

[strongm]

(so carefully check spelling)

 

[johnwm]

romij29

Please read faq222-2244 regarding double posting. It can be frustrating for you (and for us) to discover that we're approaching one simple problem in two separate threads (see thread222-975983).

As strongm says, this looks like the file name is misspelt, or the file isn't where you think it is. If P: is a network drive make sure your user has appropriate network permissions

strongm
Sorry to cut into your answer - I'll leave this one alone now!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[CubeE101]

>> (so carefully check spelling)

Navigate to the file in explorer...
Select the file,
press:
[F2] to edit the name
[Ctrl]+[C] to copy
[Esc] to cancel Rename

Then click in the Address bar or press [F6]
press:
[End] to go to the last Character
if the last character is NOT "\", then Add a "\" to the end of the path
[Ctrl]+[V] to Paste File Name
[Shift]+[Home] to select Whole Path
[Ctrl]+[C] to copy full path

Go back to VB and Paste between quotes ("")

Also, if P: is a network drive:
1) You can try using the full network pathname: \\server\path\file
2) If it is running Unix/Linux, the Path/File may be Case Sensitive...

Have Fun, Be Young... Code BASIC
-Josh

http://cubee.topcities.com

PROGRAMMER: Red-eyed, mumbling mammal capable of conversing with inanimate objects.

 

[CubeE101]

btw... It helps if you DON'T have the known extensions hidden...

Have Fun, Be Young... Code BASIC
-Josh

http://cubee.topcities.com

PROGRAMMER: Red-eyed, mumbling mammal capable of conversing with inanimate objects.

 

[romij29]

Hello Cube101,

Tried but when i PRESS F6, it takes me to the address bar which i presume is on the left hand side but i don't get an option to check if my file path has a "\" at the end or not??

Thanks.

Kojo