473,320 Members | 1,879 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Exporting Access Data to Update Excel Spreadsheet Daily

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
7 5735
ADezii
8,834 Expert 8TB
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
  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
8,834 Expert 8TB
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
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
32,556 Expert Mod 16PB
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
8,834 Expert 8TB
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
32,556 Expert Mod 16PB
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

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

Similar topics

4
by: RK | last post by:
Hi, In my application, I need to copy data from an Excel file into a SQL table. The article related to this can be found at http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B306572 ...
9
by: Mark | last post by:
I want to put out a Excel or Access database spreadsheet on a web page. Can I do this and will it let me update the spreadsheet or will it be a static web page? If not then should I put it out...
2
by: Don W. Reynolds | last post by:
Hi All, I am sent an excel spreadsheet on a daily basis. Sometimes it contains 10 rows; sometimes it contains over 5000 rows. I copy this spreadsheet into another spreadsheet and verify the...
1
by: Kaos99 | last post by:
Is there anyway of colour coding values in a report for given criteria and then exporting this formating into an Excel spreadsheet. What I have is a table full of details that are being changed...
11
by: Mr. Smith | last post by:
Hello all, My code can successfully open, write to, format and save several worksheets in a workbook then save it by a given name, close and quit excel. My problem is that if I try and do it...
15
by: daniroy | last post by:
Hello everybody, your help will be very very welcome on a very classic question... I have some Excell Macro background, a little bit of VBA knowledge. I import daily from Internet series of...
52
by: Neil | last post by:
We are running an Access 2000 MDB with a SQL 7 back end. Our network guy is upgrading to Windows Server 2003 and wants to upgrade Office and SQL Server at the same time. We're moving to SQL Server...
21
by: bobh | last post by:
Hi All, In Access97 I have a table that's greater than 65k records and I'm looking for a VBA way to export the records to Excel. Anyone have vba code to export from access to excel and have the...
12
by: slinky | last post by:
Can an Excel spreadsheet or a section of one be embedded into an Access form and serve as a subform from which other parts of the Access form can get data? Thanks!
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.