Export To Excel - User Type Not Defined

[Rik_StHelens]

Hi All,

I am exporting data from a query to an excel file using the following code from the Knowledge Base:

http://support.microsoft.com/kb/904953

I have the code set up as follows:

Public Sub WorkArounds()
On Error GoTo Leave

    Dim strSQL, SQL As String
    Dim Db As ADODB.Connection
    Set Db = New ADODB.Connection
    Db.CursorLocation = adUseClient
    Db.Open "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\MB Lister 1\My Documents\XMLCreator.accdb"
    SQL = "qryExportToExcelToXML"
    CopyRecordSetToXL SQL, Db
    Db.Close
    MsgBox "Access has successfully exported the data to excel file.", vbInformation, "Export Successful."
    Exit Sub
Leave:
        MsgBox Err.Description, vbCritical, "Error"
        Exit Sub
End Sub

Private Sub CopyRecordSetToXL(SQL As String, con As ADODB.Connection)
    Dim rs As New ADODB.Recordset
    Dim x
    Dim i As Integer, y As Integer
    Dim xlApp As Excel.Application
    Dim xlwbBook As Excel.Workbook, xlwbAddin As Excel.Workbook
    Dim xlwsSheet As Excel.Worksheet
    Dim rnData As Excel.Range
    Dim stFile As String, stAddin As String
    Dim rng As Range
    stFile = "M:\MML\ExcelToXML.xls"
    'Instantiate a new session with the COM-Object Excel.exe.
    Set xlApp = New Excel.Application
    Set xlwbBook = xlApp.Workbooks.Open(stFile)
    Set xlwsSheet = xlwbBook.Worksheets("Sheet1")
    xlwsSheet.Activate
    'Getting the first cell to input the data.
    xlwsSheet.Cells.SpecialCells(xlCellTypeLastCell).Select
    y = xlApp.ActiveCell.Column - 1
    xlApp.ActiveCell.Offset(1, -y).Select
    x = xlwsSheet.Application.ActiveCell.Cells.Address
    'Opening the recordset based on the SQL query and saving the data in the Excel worksheet.
    rs.CursorLocation = adUseClient
    If rs.State = adStateOpen Then
        rs.Close
    End If
    rs.Open SQL, con
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        x = Replace(x, "$", "")
        y = Mid(x, 2)
        Set rng = xlwsSheet.Range(x)
        xlwsSheet.Range(x).CopyFromRecordset rs
    End If
    xlwbBook.Close True
    xlApp.Quit
    Set xlwsSheet = Nothing
    Set xlwbBook = Nothing
    Set xlApp = Nothing

End Sub

I get a "Compile Error - User Defined Type Not Defined" on the 1st line of the private sub.

I am not that strong on VB so any help would be great.

Thanks for your time in advance!

 

[Beetle]

Open a VBA window, go to Tools/References and make sure you have a reference set to the Microsoft ActiveX Data Objects library.

While you're at it you also need to make sure you have a reference set to the Microsoft Excel X.0 Object Library (the X will vary depending on your version of Office).

 

[boblarson]

Also, is the database you are opening in your connection object a DIFFERENT one than the database this code is in or is it the same one the code you have here is in?

If it is the same one then you should modify your code from this:

Dim Db As ADODB.Connection
    Set Db = New ADODB.Connection
    Db.CursorLocation = adUseClient
    Db.Open "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\MB Lister 1\My Documents\XMLCreator.accdb"
    SQL = "qryExportToExcelToXML"

To this:

Dim Db As ADODB.Connection
    [B][COLOR=red]Set Db = CurrentProject.Connection[/COLOR][/B]
    Db.CursorLocation = adUseClient
    SQL = "qryExportToExcelToXML"

 

[Rik_StHelens]

Thanks for both your replies!

Beetle I have set this reference to Excel 12.0 & Active X libraries.

Bob I have changed the code as this module is calling the database in which it is located. I no longer get the compile error but I do not get an Overflow error with no further indication as to where the problem lies.

Any thoughts? If it has any bearing on things the Excel file to which I am writing already exists and is then to be manually saved as an XML file to upload to an online sales system.

Thanks again for your time, it is much appreciated!

 

[boblarson]

First off, comment out your error handler so you can get a dialog with the debug button when the error occurs. Click debug and it should take you to the offending line.

 

[jasmeetsingh89]

Hi bob ... I am getting a similar error on trying to assign values to excel from access ... the error says " A problem occurred while Microsoft Office Access was communicating with the OLE server or ActiveX Control" ...

reading your above comments, I added Microsoft activex controls in the references ... but i cannot see Microsoft Excel reference to add it in the tools/references ...

Can u please help me ...

 

[Rik_StHelens]

First off, comment out your error handler so you can get a dialog with the debug button when the error occurs. Click debug and it should take you to the offending line.

Thanks again for your reply Bob.

After running the debug it highlights the following as "Runtime Error 6: Overflow" error in the private sub:

y = Mid(x, 2)

I also get the message 'Expected variable or procedure, not module' when running the code from the 'Immediate' pane by typing 'WorkAround' and hitting enter as suggested in the original KB article.

 

[boblarson]

Thanks again for your reply Bob.

After running the debug it highlights the following as "Runtime Error 6: Overflow" error in the private sub:

y = Mid(x, 2)

I also get the message 'Expected variable or procedure, not module' when running the code from the 'Immediate' pane by typing 'WorkAround' and hitting enter as suggested in the original KB article.

First off, declare y as a LONG not an INTEGER which you currently have. (I would do X that way too).

Then, for your "Expected variable or procedure, not module" error, make sure you didn't name the module the same name as the procedure.

 

[Rik_StHelens]

Hi Bob,

Thanks again for the reply.

Your changes have got the code to run all the way through and export to a file.

However not all the data is exported. The attached file shows the exported data (sheet 1) and the data in the database table (sheet 2).

I am exporting to Excel to create and XML file to be uploaded to a sales program. Is there a better way to do this as I then need to create a mapping in Excel and install the add in pack etc?

There are currently around 70,000 records in the database.

Thanks again

Richard

 

[Beetle]

There are currently around 70,000 records in the database.

Then you won't be able to export them all unless you use a newer version of Excel. Excel 97 - 2003 (which is the version of your sample file) has a row limit of 65536. I'm not saying that is causing your current issue, but it will cause a problem at some point if you're using Excel 97-2003 (maybe you're using a newer version and just saved in the older version to upload to the forum?).

 

[md-rahim]

Dear All,

Is it possible to export table data into SPSS.

Plz help me


Rahim