473,698 Members | 1,921 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Exporting Access Data to Update Excel Spreadsheet Daily

3 New Member
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 5762
ADezii
8,834 Recognized Expert Expert
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
semijoyful
3 New Member
  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 Recognized Expert Expert
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
semijoyful
3 New Member
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 "WorkAround s" 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,569 Recognized Expert Moderator MVP
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 Recognized Expert Expert
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 "WorkAround s" 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,569 Recognized Expert Moderator MVP
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
11310
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 Using this,I am first extracting data from given excel file into a temporary DataTable. After making some operations on that DataTable (like splitting one column into two), I am saving the data into actual
9
1681
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 there as a file on the web page and open it up and then update the spreadsheet? Please advise.
2
2487
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 format of the data (some data is missing, which I can add, some 15 digit data needs to be text rather than numeric, sometimes the incoming numbers are not consistent and need leading digits, etc.). I then import this data (from the second...
1
1573
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 daily. I have a report to list these changes to give to a customer on a monthly basis but as there are 500+ changes per month, this is somewhat cumbersome. What I was planning to do, was to have certain fields that have change in a given month...
11
4049
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 again, Excel hangs. OR if I open Excel again (say from a desktop icon) before I close Access, Excel hangs. (this has happened for both 97 & 2000 for me) I of course thought that I mustn't be unloading a variable properly.
15
4211
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 data. They are setup as follow, with a few more columns on the rights as stock prices, dividend and so on as: Date Code Country Adjust Reason Name Shares 25/02/2005 FR0000045072 FR CREDIT AGRICOLE
52
9966
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 2005, and, since he already has licenses for Office Pro 2002, he wants to upgrade to that. I've been saying that we need to upgrade to Access 2003, not 2002, even if Office is kept at 2002. We are also looking to do a fair amount of...
21
6226
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 code use multiple excel tabs within a workbook???? Anyone have vba code that would create a temp table write 65,000 records to it, export those to excel, clean the temp table, append the next 65,000 records, export it to excel with a different...
12
3802
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
9152
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8887
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7709
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6515
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5858
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4360
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4613
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3037
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
1997
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.