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.ApplicationDim objWkb As Excel.WorkbookDim objSht As Excel.WorksheetDim db As DatabaseDim rs As RecordsetDim intLastCol As IntegerConst conMAX_ROWS = 20000Const 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 = NothingEnd Sub
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.
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.
joeserrone
ASKER
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
Norie
That basically means there is no worksheet called 'myImport' in the workbook you are opening.