Access, Excel 2010 - Copy Worksheet to End of Book (run-time error '91'

[Garr]

First, thanks for reading. Allow me to set up the project concept.

I have an Access database that stores athletic contest officials information. To pay the officials, I have to use an Excel cover sheet created by my company. I've managed a way to get Access to open the worksheet and drop the data from the current record on my form, but I'd like to have a one-button press to generate ALL of my cover sheets on their own tabs in a single Excel Workbook.

Objectives:
Open the workbook
do until EOF

Copy the sole sheet at the end of all the sheets
Rename the sheet based on data in the current record
Copy the current record's data onto the new worksheet
​

next record

Here's my current code. It is *not* cycling through the recordset yet because I can't even get it to work correctly for a single record.

Private Sub cmdGenerateDIVs_Click()

    'objects
    Dim objXLApp As Excel.Application
    Dim objXLBook As Excel.Workbook
    Dim objWSTemplate As Excel.Worksheet
    Dim objWS As Excel.Worksheet
    
    'variables
    Dim strRemitAdvice As String
    Dim strDescription As String
    Dim strInvoiceNo As String
    Dim strSheetName As String
    
    'object setup
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open("O:\ATH_BIZ\2012-2013\01 Men's Sports\MVB - Men's Volleyball\OfficialsContracts\MVBOfficialsDIVs.xlsx")
    Set objWSTemplate = objXLBook.Sheets(1)
      
    objXLApp.Application.Visible = True
    
    'saving the document as a test document
    objXLBook.SaveAs "O:\ATH_BIZ\2012-2013\01 Men's Sports\MVB - Men's Volleyball\OfficialsContracts\" & Me.OfficialLast & " " & Me.OfficialFirst & " TEST"
    
    strRemitAdvice = "*IPFW vs " & Me.AwayTeam_TeamAbbreviation & " " & Me.MatchDate & " " & Me.OfficialTypeAbbr
    strDescription = "Men's Volleyball Official " & strRemitAdvice
    strInvoiceNo = "SRVC" & Format(Month(Me.MatchDate), "00") & Format(Day(Me.MatchDate), "00") & Format(Year(Me.MatchDate), "YYYY")
    strSheetName = Me.OfficialLast & Format(Month(Me.MatchDate), "00") & Format(Day(Me.MatchDate), "00")
    
    'creating a copy of the main DIV and renaming it
    objWS = objXLBook.Worksheets(objWSTemplate.Name).Copy(After:=objXLBook.Sheets(Worksheets.Count))
    objWS.Name = "Test"
        
    'placing the data from the form into the excel spreadsheet
    With objXLApp.ActiveSheet
        .Range("D2").Value = Me.VendorNumber
        .Range("F1").Value = Me.TIN
        .Range("A4").Value = Me.AddrName
        .Range("A5").Value = Me.Address
        .Range("A6").Value = Me.CSZ
        .Range("A10").Value = strRemitAdvice
        .Range("D13").Value = Me.OfficialTypePay
        .Range("D14").Value = Me.RoundTrip
        .Range("D15").Value = Me.Mileage
        .Range("B18").Value = strDescription
        .Range("F35").Value = Date
        .Range("G16").Value = strInvoiceNo
    End With

End Sub

When I run that code, it copies the sheet to the end, but cannot move forward. I get a run-time error, and I can't figure out why.

Run-Time error '91': Object variable or With block variable not set

I've searched for about two hours trying to make this work, and I can't find anything that helps. Can anyone assist? What further do you need from me?

 

[Norie]

Copying a worksheet doesn't return an object.

Try this.

objXLBook.Worksheets(objWSTemplate.Name).Copy(After:=objXLBook.Sheets(objXLBook.Worksheets.Count))

Set objWS = objXLBook.Sheets(objXLBook.Worksheets.Count)

objWS.Name = "Test"

With objWS

PS You can use this to format MatchDate.

   strInvoiceNo = "SRVC" & Format(Me.MatchDate, "mm/dd/yyyy")

 

[strive4peace]

when saving objXLBook, it would be best to construct the filename in a string variable so you can see what it is. Also, you are missing the file extension such as .xls or .xlsx

objWS is an object and must be assigned using SET

set objWS = ...

also, add an error handler to your code to you can see exactly where the problem is.

Error Handling
VBA Error Handler By Crystal

 

[strive4peace]

after you read the short page on error handling, you should release object variables in the exit code (statements after Proc_Exit) otherwise that instance of Excel will stay open

set objWS = nothing
set objWSTemplate = nothing
if not objXLBook is nothing then
   'close without saving
   objXLBook.close 0
   set objXLBook = nothing
end if
if not objXLApp is nothing then
   objXLApp.quit
   set objXLApp= nothing
end if

 

[Garr]

This line keeps throwing an error:

objXLBook.Worksheets(objWSTemplate.Name).Copy(After:=objXLBook.Sheets(objXLBook.Worksheets.Count))

Compile Error: Expected: =

Thanks for the invoice advice. I wanted the date with leading zeroes and no slashes, so i just used "mmddyyyy". Don't know why I missed that. DOH!

 

[Norie]

Crystal

You can't use Set to get a reference to the copied worksheet - Copy returns a boolean value.

 

[strive4peace]

thanks, Norie, I simply said SET must be used ...

when copying, the new sheet is the ActiveSheet, then SET can be used to create a reference

 

[Norie]

Apologies, I thought you were suggesting adding SET to the copy line.

 

[strive4peace]

no problem, Norie -- best to clear this up for others too

> "Compile Error: Expected: ="

since you are not handling a return value, remove the parentheses around the 'After' argument


objXLBook.Worksheets(objWSTemplate.Name).Copy After:=objXLBook.Sheets(objXLBook.Worksheets.Count)

 

[Garr]

Thanks for your help, guys. I still can't get this to work. When I try it Norie's way, it doesn't compile. I'm clearly missing something. I can't seem to find a way to make this work. . .at all.

It should be simple enough, but it's just not working.