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
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