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
-
7 5735
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
-
- Have you set a Reference to the Microsoft Excel Object Library XX.X?
- If this doesn't fix the problem, do you know how to set a Breakpoint in Code?
- Have you set a Reference to the Microsoft Excel Object Library XX.X?
- 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
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: - In any Code View Window.
- Tools ==> References
- Scroll down to Microsoft Excel XX.X Object Library
- Select the Check Box
- OK
- Try executing the code again.
Before getting into Breakpoints, try setting a Reference to the Library if it doesn't already exist:- In any Code View Window.
- Tools ==> References
- Scroll down to Microsoft Excel XX.X Object Library
- Select the Check Box
- OK
- 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 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.
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 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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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!
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
| |