Exporting Access Data to Update Excel Spreadsheet Daily

[semijoyful]

OS: Win XP SP2
Access version: 2003
Excel version: 2003

I am new at this, as I am sure you have gathered from this post title:)
I am working on a form where users can input data in Access and at the end of the day be able to press a button on the form that would update that data to a single exel spreadsheet. The following is the code for the module. When I click on the button in the form, It takes me to VBS Debugger. I then type WorkArounds to run the code, and it gives me the following error.

'Compile error
user-defined type not defined'

As green as I am, I tried to troubleshoot this issue. I took the table data, exported that table data into a fresh spreadsheet, and defined the data type for all of the heads (except Yes/No -those remained generic). Any help would be much appreciated!

-Kirk

Here's the code:

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.Jet.OLEDB.4.0;Data Source=H:\YaleNote Database Backup\PILOT\WellNote Pilot DB.mdb"
    SQL = "SELECT [Cholesterol 2c].* FROM [Cholesterol 2c];"
    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 = "H:\YaleNote Database Backup\PILOT\Cholesterol 2c.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("Cholesterol_2c")
    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
 

[ADezii]

OS: Win XP SP2
Access version: 2003
Excel version: 2003

I am new at this, as I am sure you have gathered from this post title:)
I am working on a form where users can input data in Access and at the end of the day be able to press a button on the form that would update that data to a single exel spreadsheet. The following is the code for the module. When I click on the button in the form, It takes me to VBS Debugger. I then type WorkArounds to run the code, and it gives me the following error.

'Compile error
user-defined type not defined'

As green as I am, I tried to troubleshoot this issue. I took the table data, exported that table data into a fresh spreadsheet, and defined the data type for all of the heads (except Yes/No -those remained generic). Any help would be much appreciated!

-Kirk

Here's the code:

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.Jet.OLEDB.4.0;Data Source=H:\YaleNote Database Backup\PILOT\WellNote Pilot DB.mdb"
    SQL = "SELECT [Cholesterol 2c].* FROM [Cholesterol 2c];"
    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 = "H:\YaleNote Database Backup\PILOT\Cholesterol 2c.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("Cholesterol_2c")
    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
 

1. Have you set a Reference to the Microsoft Excel Object Library XX.X?
2. If this doesn't fix the problem, do you know how to set a Breakpoint in Code?

[semijoyful]

1. Have you set a Reference to the Microsoft Excel Object Library XX.X?
2. If this doesn't fix the problem, do you know how to set a Breakpoint in Code?

Thanks for the reply! No, I don't believe I have set a reference to the above object library. I am not very clear on what or how to go about doing that or the breakpoint. I am a layman in this area that has taken upon the task to develop a form for a free health and wellness coaching service. Any instruction or direction would be much appreciated.

Cheers!

Kirk

[ADezii]

Thanks for the reply! No, I don't believe I have set a reference to the above object library. I am not very clear on what or how to go about doing that or the breakpoint. I am a layman in this area that has taken upon the task to develop a form for a free health and wellness coaching service. Any instruction or direction would be much appreciated.

Cheers!

Kirk

Before getting into Breakpoints, try setting a Reference to the Library if it doesn't already exist:

1. In any Code View Window.
2. Tools ==> References
3. Scroll down to Microsoft Excel XX.X Object Library
4. Select the Check Box
5. OK
6. Try executing the code again.

[semijoyful]

Before getting into Breakpoints, try setting a Reference to the Library if it doesn't already exist:

1. In any Code View Window.
2. Tools ==> References
3. Scroll down to Microsoft Excel XX.X Object Library
4. Select the Check Box
5. OK
6. Try executing the code again.

Great! The code seemed to have worked, but there's one pesky thing that remains:

when I went to the form and executed the code, via the a command button, it took me back to VBS. I typed "WorkArounds" in the Immediate window. It then returned back to the form with a pop-up, saying that the excel spreadsheet was updated successfully. It seems to have worked, but I don't want our coaches to be directed to VBS. That could be a nightmare. How does one get around this?

Correction: all of the described occured, but I don't notice any change to the specified spreadsheet :(

Thanks for taking the time to help me figure this out btw!

-Kirk

[NeoPa]

Kirk, try posting this as a new question.
Sometimes the notifications get cancelled or overlooked - maybe ADezii has missed your last post. Maybe he'll get a new notification with my post.

[ADezii]

Great! The code seemed to have worked, but there's one pesky thing that remains:

when I went to the form and executed the code, via the a command button, it took me back to VBS. I typed "WorkArounds" in the Immediate window. It then returned back to the form with a pop-up, saying that the excel spreadsheet was updated successfully. It seems to have worked, but I don't want our coaches to be directed to VBS. That could be a nightmare. How does one get around this?

Correction: all of the described occured, but I don't notice any change to the specified spreadsheet :(

Thanks for taking the time to help me figure this out btw!

-Kirk

What exactly do you mean by?

it took me back to VBS

[NeoPa]

What exactly do you mean by?

it took me back to VBS

I thought he meant the VBA code window.
He's probably tracing or breakpointing or something.