Avatar of joeserrone
 asked on

Access to Excel

I have the following code that is intended to take the information from a MS Access Table to a preformatted Excel spreadsheet on my desktop called Export.xls that has some protections in various fields. When I try running it I get the debugging error message "Compile Error: User-defined type not defined"

Any suggestions how to resolve this?

Private Sub Command11_Click()
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As Database
Dim rs As Recordset
Dim intLastCol As Integer
Const conMAX_ROWS = 20000
Const conSHT_NAME = "myImport"
Const conWKB_NAME = "C:\users\joe\desktop\Export.xls"
  Set db = CurrentDb
  Set objXL = New Excel.Application
  Set rs = db.OpenRecordset("myImport", dbOpenSnapshot)
  With objXL
    .Visible = True
    Set objWkb = .Workbooks.Open(conWKB_NAME)
    'Give book a new name
    'objWkb.SaveAs "NewName.xlsx"
    Set objSht = objWkb.Worksheets(conSHT_NAME)

    With objSht
      .Range("A2").CopyFromRecordset rs
    End With
  End With
  Set objSht = Nothing
  Set objWkb = Nothing
  Set objXL = Nothing
  Set rs = Nothing
  Set db = Nothing

End Sub

Open in new window

Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Got it! thanks!

The suggestion I made was for Access.

1 Open the VBE (ALT+F11).

2 Goto Tools>References...

3 Scroll down list until you find Microsoft Excel x.0 Object Library - the x.0 is
    the version, eg Excel 2010 is 14.0

4 Check that reference and hit OK

5 That's it.

ok got it, was in the wrong section. I ran the code and it opens the template spreadsheet, does not copy anything, and gives me a Run-tim error '9': subscript out of range

Looks like it stopps right at    
Set objSht = objWkb.Worksheets(conSHT_NAME)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

That basically means there is no worksheet called 'myImport' in the workbook you are opening.