Method 'Open' of objects 'Workbooks' failed

[Ratman11]

Hi
i posted a problem i was having with this code last week - when i ran it the first time it ran ok, the second time i got the above error.

I got some new code and pasted it in & it worked fine. I have now come to run it again today, and on the first run I am getting the above error on the highlighted line.

if it helps, i'm running this on access 97

Any clues?

Private Sub Command2_Click()
Dim objex As Excel.Application
Set objex = New Excel.Application

If Nz(WeekNumber, 0) = 0 Then
MsgBox "A valid week number is required"
Else
If Nz(Year, 0) = 0 Then
MsgBox "A valid year is required"
Else
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryRWAReport02", "H:\Service Centre\Reports\Wkly Reports\" & Year & "\wk" & " " & WeekNumber & "\RWAReportwk" & WeekNumber & ".xls", True
End If
End If

'format excel spreadsheet

[highlight]objex.Workbooks.Open "H:\Service Centre\Reports\Wkly Reports\" & Year & "\wk" & " " & WeekNumber & "\RWAReportwk" & WeekNumber & ".xls"[\highlight]
'objex.Sheets("qryRWAReport01").Activate
'objex.Visible = True
'objex.ScreenUpdating = False
With objex.Worksheets("qryRWAReport02").Cells.Font
.Name = "Arial"
.Size = 9
End With
objex.Worksheets("qryRWAReport02").Columns.AutoFit
objex.Range("A2").Select
objex.ActiveWindow.FreezePanes = True
With objex.ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
objex.ActiveSheet.PageSetup.PrintArea = ""
With objex.ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&A"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P"
.RightFooter = ""
.LeftMargin = objex.InchesToPoints(0.75)
.RightMargin = objex.InchesToPoints(0.75)
.TopMargin = objex.InchesToPoints(1)
.BottomMargin = objex.InchesToPoints(1)
.HeaderMargin = objex.InchesToPoints(0.5)
.FooterMargin = objex.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
objex.ScreenUpdating = True
objex.Workbooks("RWAReportwk" & WeekNumber & ".xls").Save
objex.Workbooks("RWAReportwk" & WeekNumber & ".xls").Close
objex.Quit
Set objex = Nothing

 

[Ratman11]

sorry, did the highlighting incorrectly...it should be on this line:

[highlight]objex.Workbooks.Open "H:\Service Centre\Reports\Wkly Reports\" & Year & "\wk" & " " & WeekNumber & "\RWAReportwk" & WeekNumber & ".xls"[/highlight]

 

[PHV]

No error on this line ?
If Nz(Year, 0) = 0 Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ratman11]

nope. this code is running off a form that has two text boxes - year & week. that is where those bits of info are coming from.

does it matter what references i have running?

 

[jebry]

Hi!

It is possible that the query isn't done exporting before you try to open the file. Try adding a message box in the code between the export and the opening of the file:

Call MsgBox("The file " & all your file name stuff here & " has been created")

This will force the user to reply to the message box and give the query time to export.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[Ratman11]

Thanks Jeff, the message box fixed it.