Error 1004 generated when debugging code.Need immediate assistance

[romij29]

Hello All,

I am trying to execute a VB program that will copy worksheets in a workbook into another workbook with some calculations done to it. However, after writing basic code and trying to debug I came across the message"Error 1004- Application or Object-defined error" at line 6(when opening first filename) and I am sure line 7 will give me the same prob.


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
Workbooks.Open Filename:=strPath & strCSV

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

Thank you and reply A.S.A.P

romij29

 

[strongm]

Can you confirm that strPath & strTemplate actually results in a legitimate path to the file (1004 is actually Excel saying 'file not found')?

Often the error in these situations is that the bit that makes up strPath doesn't have a trailing '\', and it should do...

 

[romij29]

Thanks for your reply strongm,

I have checked it and there is a trailing "\".
Range "E2" in line 3 of code is actually my file path C:\ and strTemplate and strCSV are in these files. Line 3 refers to a control worksheet that sort of directs the macro where to go and get files needed for execution.

 

[strongm]

Well, it can't find the file. As I say, 1004 is Excel's "file not found" error

 

[romij29]

STRONG M,

Do you think the overall code is all right though?And I shdn't have any major probs??

 

[romij29]

now getting "400" , for that same line, strongm?? Need some help as i am a novice in VB??Thanks

 

[johnwm]

Change this code :
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
Workbooks.Open Filename:=strPath & strCSV

to be:

strPath = Workbooks("Budget_Macro.xls").Sheets("Budget").Range("E2")
strTemplate = "All Cinemas Phased.xls"
strCSV = "Cinemas CSV 2005.xls"
Application.StatusBar = "Opening Files"
myPath = strPath & strTemplate
debug.print myPath
'Workbooks.Open Filename:=strPath & strTemplate
'Workbooks.Open Filename:=strPath & strCSV

Then copy & paste the answer from the Immediate window back here. That will tell us exactly what you're passing to the .Open statement

________________________________________________________________
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

 

[romij29]

JohnWM,

Done what u asked ,however,it just skips the problem to tell me at line "
Workbooks(strTemplate).Sheets(strCinema).Range("D77") = strRate"
that there is an error '9':Subscript out of range. So I am utterly confused and could you explain to a novice what an Immediate window is,thanks.I need help on this urgently.All I want is to copy a range of worksheets in a workbook to another workbook.I have checked my file paths and there seems to be no problems at all.I have run this macro before but deleted the original "All Cinemas Phased file "and replaced with an updated one so what could be the problem.

Do you like want a printout of the problem with the debug.print myath command??
I am new to this so layman's terms would be a great help.

Thanks
romij29

 

[romij29]

Got your instruction, Johnwm and here is what is in the immediate window:

P:\All Cinemas Phased.xls

Copied and pasted ditto!!
Thanks.

 

[romij29]

Hello All need help on this one. Know its New Years eve ,hahaha!!!!

 

[johnwm]

That should work fine as long as the file All Cinemas Phased.xls is in the root folder of your P: drive

________________________________________________________________
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