473,581 Members | 2,670 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Get data from Access form into Excel using ADO

4 New Member
Hello all,

I'm trying to transfer the value of a control on an Access form to an Excel worksheet using VBA. The Access form is a single form with all the controls disabled, so that data is "read only." I have a button on the form which opens the Excel worksheet, which then basically serves as the input form for the Access database.

What I need is to somehow get the primary key (EstimateID) of the current record on the Access form transferred to the Excel worksheet, then use ADO and ".find" to locate that record in the Access form's query and gather the rest of the data.

Right now my code is in an Excel module that I run in the "Workbook_O pen" event.

Expand|Select|Wrap|Line Numbers
  1. Private Const DatabasePath As String = "C:\Documents and Settings\Owner\My Documents\Storm Shield Database.mdb"
  2. Dim strEstimateID As String
  3.  
  4.  
  5. Sub TransferFromDatabase()
  6.     Dim AdConnection As ADODB.Connection, Estimate As ADODB.Recordset
  7.  
  8.     ' Connect to the Access database
  9.     AdConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
  10.     "Data Source= " & DatabasePath & "; Persist Security Info=False"
  11.  
  12.     ' Open a recordset
  13.     Estimate.Open "qryEstimates", AdConnection
  14.  
  15.     ' Find the current estimate based on the EstimateID
  16.     Estimate.Find "EstimateID = " & strEstimateID
  17.  
  18.     ' Add a new estimate if an existing one isn't found
  19.     If EOF = True Then Estimate.AddNew
  20.  
  21.     ' Fill the appropriate cells with data from the database
  22.     ActiveSheet.Cells(I6) = Estimate.Fields(EstimateID).Value
  23.      *
  24.      *
  25.      *
  26.  
  27.     ' Close the recordset and connection
  28.     Estimate.Close
  29.     AdConnection.Close
  30.     Set Estimate = Nothing
  31.     Set AdConnection = Nothing
  32. End Sub
  33.  
As you can see in line 15, I'm trying to use the variable strEstimateID as the criteria in the "Estimate.F ind" command. However, at no point do I set the value of strEstimateID, because I don't know how to get that data from the control on the Access form.

I'm sure there are errors in the code, and the code is not finished, but I can't debug it until I find a method to get the primary key transferred into Excel. If you spot error, feel free to point them out, but my focus is really on the problem of the primary key.



...Not sure if it's relevant, but here is the code for the Access button which opens the Excel worksheet...

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnOpenEstimate_Click()
  2.  
  3.     ' Set path and filename of the estimate sheet to open
  4.     strEstimateSheetPath = "I:\Miscellaneous\Database Files\Estimate Sheets\"
  5.     strEstimateSheetName = ProjectName & " -" & Str(EstimateID) & ".xls"
  6.  
  7.     ' If new record: open new estimate sheet -- else: open corresponding estimate sheet
  8.     If IsNull(EstimateID) Then
  9.          Call ShellExecute(0, "", strEstimateSheetPath & "Estimate Sheet.xls", "", "", 2)
  10.     Else
  11.          Call ShellExecute(0, "", strEstimateSheetPath & strEstimateSheetName, "", "", 2)
  12.     End If
  13. End Sub

I'm not a programmer (well...2 years of Basic in HS :-) ), so keep that in mind in your answers.

P.S. - I could probably use the clipboard to transfer the value of the control on the Access form, but I want to avoid that if possible.
Jul 20 '08 #1
7 13158
ADezii
8,834 Recognized Expert Expert
  1. Link the Excel Worksheet to the Current Database, now it can be treated as any Local Table would be.
  2. Create a Form and set its Record Source equal to the Linked Worksheet.
  3. Updating the Form will update the Worksheet, and you can now easily transfer values between Forms.
Jul 20 '08 #2
kpresidente
4 New Member
  1. Link the Excel Worksheet to the Current Database, now it can be treated as any Local Table would be.
  2. Create a Form and set its Record Source equal to the Linked Worksheet.
  3. Updating the Form will update the Worksheet, and you can now easily transfer values between Forms.
Thanks ADezii, but unfortunately I can't do that. Each record in the Access table is associated with a seperate excel workbook, saved under a unique filename, but having the same structure (basically a template).

What I'm trying to do is similar to passing an argument from one access form to another, only for me, the second "form" is really an Excel workbook.

It's a poor use of Excel, but I have reasons for doing it this way.

Right now I can only think of two ways of going about it:

1. Pass the variable through a text file, but I imagine that would be very slow. I'd have to open the file from access -> pass the variable -> save the file-> close it -> open it from Excel -> get the variable -> close the file. That seems like a lot.

1. Pass the variable through the clipboard, which would probably be fast enough, but I don't want to be clearing the clipboard in case the user has something else on it.

So I'm looking for another way....
Jul 21 '08 #3
ADezii
8,834 Recognized Expert Expert
Thanks ADezii, but unfortunately I can't do that. Each record in the Access table is associated with a seperate excel workbook, saved under a unique filename, but having the same structure (basically a template).

What I'm trying to do is similar to passing an argument from one access form to another, only for me, the second "form" is really an Excel workbook.

It's a poor use of Excel, but I have reasons for doing it this way.

Right now I can only think of two ways of going about it:

1. Pass the variable through a text file, but I imagine that would be very slow. I'd have to open the file from access -> pass the variable -> save the file-> close it -> open it from Excel -> get the variable -> close the file. That seems like a lot.

1. Pass the variable through the clipboard, which would probably be fast enough, but I don't want to be clearing the clipboard in case the user has something else on it.

So I'm looking for another way....
If it is simply a matter of passing a Variable from Access to Excel, aside from Automation code, the least cost effective and simplest approach would seem to be writing the Variable to the System Registry in Access via the SaveSetting Statement then retrieving it in Excel via the GetSetting() Function. You seem to have the other logic already figured out. Make sense to you? If you like, I can demo a simple interchange for you.
Jul 21 '08 #4
kpresidente
4 New Member
If it is simply a matter of passing a Variable from Access to Excel, aside from Automation code, the least cost effective and simplest approach would seem to be writing the Variable to the System Registry in Access via the SaveSetting Statement then retrieving it in Excel via the GetSetting() Function. You seem to have the other logic already figured out. Make sense to you? If you like, I can demo a simple interchange for you.
Maybe something explaining the syntax a little? Access' help file for SaveSetting() and GetSetting() is a little vague and I'm not very familiar with registry keys.
Jul 21 '08 #5
ADezii
8,834 Recognized Expert Expert
Maybe something explaining the syntax a little? Access' help file for SaveSetting() and GetSetting() is a little vague and I'm not very familiar with registry keys.
  1. Within Employee Form in Access
    Expand|Select|Wrap|Line Numbers
    1. Dim intEmpID As Integer
    2.  
    3. intEmpID = Me![EmployeeID]
    4.  
    5. 'Save the value of the Employee's ID (Primary Key) in the [EmployeeID]
    6. 'Field of the Current Form to the Registry.
    7. SaveSetting appname:="Variable Transfer Demo", Section:="Employee Data", _
    8.             Key:="Identifier", setting:=intEmpID
  2. Within the Open() Event of a Workbook in Excel
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Workbook_Open()
    2. Dim intEmployeeID As Integer
    3.  
    4. 'Retrieve the Employee ID from the Workbook Open() Event in Excel for
    5. 'the previously saved value in Access, if not found return 99999
    6. intEmployeeID = GetSetting(appname:="Variable Transfer Demo", Section:="Employee Data", _
    7.                            Key:="Identifier", Default:=99999)
    8.  
    9. 'Test the retrieval
    10. Debug.Print "The value of the EmployeeID retrieved in Excel is: " & intEmployeeID
    11.  
    12. 'Assign the Employee ID to Cell $F$10 on Sheet1
    13. Worksheets("Sheet1").Cells(10, 6) = intEmployeeID
    14. End Sub
  3. The Key here is that both Office Components will refer to the same location in the System Registry (look up the Section/Key/Value with Regedit.exe).
  4. Code has been tested and is fully operational.
  5. Let me know how you make out, any questions feel free to ask.
Jul 21 '08 #6
kpresidente
4 New Member
  1. Within Employee Form in Access
    Expand|Select|Wrap|Line Numbers
    1. Dim intEmpID As Integer
    2.  
    3. intEmpID = Me![EmployeeID]
    4.  
    5. 'Save the value of the Employee's ID (Primary Key) in the [EmployeeID]
    6. 'Field of the Current Form to the Registry.
    7. SaveSetting appname:="Variable Transfer Demo", Section:="Employee Data", _
    8.             Key:="Identifier", setting:=intEmpID
  2. Within the Open() Event of a Workbook in Excel
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Workbook_Open()
    2. Dim intEmployeeID As Integer
    3.  
    4. 'Retrieve the Employee ID from the Workbook Open() Event in Excel for
    5. 'the previously saved value in Access, if not found return 99999
    6. intEmployeeID = GetSetting(appname:="Variable Transfer Demo", Section:="Employee Data", _
    7.                            Key:="Identifier", Default:=99999)
    8.  
    9. 'Test the retrieval
    10. Debug.Print "The value of the EmployeeID retrieved in Excel is: " & intEmployeeID
    11.  
    12. 'Assign the Employee ID to Cell $F$10 on Sheet1
    13. Worksheets("Sheet1").Cells(10, 6) = intEmployeeID
    14. End Sub
  3. The Key here is that both Office Components will refer to the same location in the System Registry (look up the Section/Key/Value with Regedit.exe).
  4. Code has been tested and is fully operational.
  5. Let me know how you make out, any questions feel free to ask.

That worked excellently! Thank you so much!

That's a great tool. I'll use that a lot.
Jul 22 '08 #7
ADezii
8,834 Recognized Expert Expert
That worked excellently! Thank you so much!

That's a great tool. I'll use that a lot.
You are quite welcome, enjoy.
Jul 22 '08 #8

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

Similar topics

2
5295
by: Niyazi | last post by:
Hi, I have to retrieve a data from AS400 DB2 and after working with data I have to export into one of existing Excel file. I can connect into specific library in AS400 DB2 using AS400 Client-Access v5.2 program using (in VB.NET) ODBC driver (DSN Name …) . I can retrieve datam work on it using VB.NET and I can send into 'NEW' Excel file....
4
13461
by: Martin | last post by:
There is an Access table on the network. 15 users who do not have Access are connected to the network. Is there a way for each user to be able to enter one or more rows containing 3 or 4 columns to Excel on his machine and then press a button or something and append that data to the Access table on the network? Any suggestions on what the code...
8
8077
by: Colleyville Alan | last post by:
I have been working on an Access app that takes info from a file and writes it to a spreadsheet on a form, simultaneously saving the spreadsheet to Excel. I got the idea that the same concept could work in reverse, i.e. we have a cost model written in Excel that calculates the profitability of customer accounts based on several inputs and...
7
3047
by: Darren | last post by:
I have been attempting to create a reservation planning form in excel that imports Data from an Access database and inserts that information automaticly into the correct spreed sheet and the correct cells within that worksheet. The Excel Book is made up of 12 worksheets named Jan-Dec. Each worksheet has columns labeled as each day of that...
5
4071
by: Jonny | last post by:
Hello, I have created a button on my form which when pressed does the following : 1) Run a pre-defined Macro, which is picking up a query and running my data to excel. However, I need the data to export into Excel in a certain format, i.e it needs to begin importing at cell A4, and in truth it would be great
2
1802
by: JM | last post by:
Hi I have created a Windows Form that takes input. When the Run button is pressed the form is disabled and the code checks some files and inputs the data into an Excel worksheet (that is hidden so that I cannot watch as it gets filled up). When it is done it saves the Excel file. I find that if I go into another program while it's...
1
1705
by: Herbert Chan | last post by:
Hello, I'm subscribing to funds on HSBC, and the fund prices can be found here: http://www.tools.hsbc.com.hk/search/mpf?id=hk+mpf+fundsearch&lang=en&init=true At the moment, I'm copying each day's data into an excel spreadsheet (I'm using the query function of excel to automatically extract the data from the webpage). So now, the Access...
7
5754
by: semijoyful | last post by:
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...
2
3882
by: eko99312 | last post by:
Dear All, As you know that input data on excel is easy than access, you can simple click the date that you prefer and input the value that you want. For example : Consider that the month that I use is November 2009 : If i use excel it will look like this, Country Day in a month 1 2 3 4 5 Australia ...
0
7869
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8151
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. ...
0
8306
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8176
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5677
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...
0
3806
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...
1
2302
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
1
1404
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1139
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...

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.