By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,137 Members | 2,242 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,137 IT Pros & Developers. It's quick & easy.

Exporting Access Data to Update Excel Spreadsheet Daily

P: 3
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:

Expand|Select|Wrap|Line Numbers
  1. Public Sub WorkArounds()
  2. On Error GoTo Leave
  3.  
  4.     Dim strSQL, SQL As String
  5.     Dim Db As ADODB.Connection
  6.     Set Db = New ADODB.Connection
  7.     Db.CursorLocation = adUseClient
  8.     Db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=H:\YaleNote Database Backup\PILOT\WellNote Pilot DB.mdb"
  9.     SQL = "SELECT [Cholesterol 2c].* FROM [Cholesterol 2c];"
  10.     CopyRecordSetToXL SQL, Db
  11.     Db.Close
  12.     MsgBox "Access has successfully exported the data to excel file.", vbInformation, "Export Successful."
  13.     Exit Sub
  14. Leave:
  15.         MsgBox Err.Description, vbCritical, "Error"
  16.         Exit Sub
  17. End Sub
  18.  
  19. Private Sub CopyRecordSetToXL(SQL As String, con As ADODB.Connection)
  20.     Dim rs As New ADODB.Recordset
  21.     Dim x
  22.     Dim i As Integer, y As Integer
  23.     Dim xlApp As Excel.Application
  24.     Dim xlwbBook As Excel.Workbook, xlwbAddin As Excel.Workbook
  25.     Dim xlwsSheet As Excel.Worksheet
  26.     Dim rnData As Excel.Range
  27.     Dim stFile As String, stAddin As String
  28.     Dim rng As Range
  29.     stFile = "H:\YaleNote Database Backup\PILOT\Cholesterol 2c.xls"
  30.     'Instantiate a new session with the COM-Object Excel.exe.
  31.     Set xlApp = New Excel.Application
  32.     Set xlwbBook = xlApp.Workbooks.Open(stFile)
  33.     Set xlwsSheet = xlwbBook.Worksheets("Cholesterol_2c")
  34.     xlwsSheet.Activate
  35.     'Getting the first cell to input the data.
  36.     xlwsSheet.Cells.SpecialCells(xlCellTypeLastCell).Select
  37.     y = xlApp.ActiveCell.Column - 1
  38.     xlApp.ActiveCell.Offset(1, -y).Select
  39.     x = xlwsSheet.Application.ActiveCell.Cells.Address
  40.     'Opening the recordset based on the SQL query and saving the data in the Excel worksheet.
  41.     rs.CursorLocation = adUseClient
  42.     If rs.State = adStateOpen Then
  43.         rs.Close
  44.     End If
  45.     rs.Open SQL, con
  46.     If rs.RecordCount > 0 Then
  47.         rs.MoveFirst
  48.         x = Replace(x, "$", "")
  49.         y = Mid(x, 2)
  50.         Set rng = xlwsSheet.Range(x)
  51.         xlwsSheet.Range(x).CopyFromRecordset rs
  52.     End If
  53.     xlwbBook.Close True
  54.     xlApp.Quit
  55.     Set xlwsSheet = Nothing
  56.     Set xlwbBook = Nothing
  57.     Set xlApp = Nothing
  58.  
  59. End Sub
  60.  
May 21 '07 #1
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,669
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:

Expand|Select|Wrap|Line Numbers
  1. Public Sub WorkArounds()
  2. On Error GoTo Leave
  3.  
  4.     Dim strSQL, SQL As String
  5.     Dim Db As ADODB.Connection
  6.     Set Db = New ADODB.Connection
  7.     Db.CursorLocation = adUseClient
  8.     Db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=H:\YaleNote Database Backup\PILOT\WellNote Pilot DB.mdb"
  9.     SQL = "SELECT [Cholesterol 2c].* FROM [Cholesterol 2c];"
  10.     CopyRecordSetToXL SQL, Db
  11.     Db.Close
  12.     MsgBox "Access has successfully exported the data to excel file.", vbInformation, "Export Successful."
  13.     Exit Sub
  14. Leave:
  15.         MsgBox Err.Description, vbCritical, "Error"
  16.         Exit Sub
  17. End Sub
  18.  
  19. Private Sub CopyRecordSetToXL(SQL As String, con As ADODB.Connection)
  20.     Dim rs As New ADODB.Recordset
  21.     Dim x
  22.     Dim i As Integer, y As Integer
  23.     Dim xlApp As Excel.Application
  24.     Dim xlwbBook As Excel.Workbook, xlwbAddin As Excel.Workbook
  25.     Dim xlwsSheet As Excel.Worksheet
  26.     Dim rnData As Excel.Range
  27.     Dim stFile As String, stAddin As String
  28.     Dim rng As Range
  29.     stFile = "H:\YaleNote Database Backup\PILOT\Cholesterol 2c.xls"
  30.     'Instantiate a new session with the COM-Object Excel.exe.
  31.     Set xlApp = New Excel.Application
  32.     Set xlwbBook = xlApp.Workbooks.Open(stFile)
  33.     Set xlwsSheet = xlwbBook.Worksheets("Cholesterol_2c")
  34.     xlwsSheet.Activate
  35.     'Getting the first cell to input the data.
  36.     xlwsSheet.Cells.SpecialCells(xlCellTypeLastCell).Select
  37.     y = xlApp.ActiveCell.Column - 1
  38.     xlApp.ActiveCell.Offset(1, -y).Select
  39.     x = xlwsSheet.Application.ActiveCell.Cells.Address
  40.     'Opening the recordset based on the SQL query and saving the data in the Excel worksheet.
  41.     rs.CursorLocation = adUseClient
  42.     If rs.State = adStateOpen Then
  43.         rs.Close
  44.     End If
  45.     rs.Open SQL, con
  46.     If rs.RecordCount > 0 Then
  47.         rs.MoveFirst
  48.         x = Replace(x, "$", "")
  49.         y = Mid(x, 2)
  50.         Set rng = xlwsSheet.Range(x)
  51.         xlwsSheet.Range(x).CopyFromRecordset rs
  52.     End If
  53.     xlwbBook.Close True
  54.     xlApp.Quit
  55.     Set xlwsSheet = Nothing
  56.     Set xlwbBook = Nothing
  57.     Set xlApp = Nothing
  58.  
  59. End Sub
  60.  
  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?
May 22 '07 #2

P: 3
  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
May 22 '07 #3

ADezii
Expert 5K+
P: 8,669
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.
May 22 '07 #4

P: 3
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
May 22 '07 #5

NeoPa
Expert Mod 15k+
P: 31,616
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.
Jun 2 '07 #6

ADezii
Expert 5K+
P: 8,669
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
Jun 2 '07 #7

NeoPa
Expert Mod 15k+
P: 31,616
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.
Jun 2 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.