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

Get data from Access form into Excel using ADO

P: 4
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_Open" 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.Find" 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
Share this Question
Share on Google+
7 Replies


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

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

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

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

Post your reply

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