Copy Excel data to Access Form (1 Viewer)

GaleT

Registered User.
Local time
Today, 06:15
Joined
Oct 18, 2019
Messages
72
Hi,


I am trying to find a VBA example that copies Excel data from specific cells in a Worksheet to an Access Form. All I am finding is examples of Access import.



Can anyone direct me to a reference or provide an example?



Gale
 

GaleT

Registered User.
Local time
Today, 06:15
Joined
Oct 18, 2019
Messages
72
Hi theDBguy,


By "Excel Automation" do you mean I should handle this from Excel rather than from Access?


Gale
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:15
Joined
Oct 29, 2018
Messages
21,357
Hi theDBguy,

By "Excel Automation" do you mean I should handle this from Excel rather than from Access?

Gale
No. I am saying you can use Excel Automation to "grab" some data from an Excel file from within Access.


PS. You may have missed the link I added to my original response above.
 

GaleT

Registered User.
Local time
Today, 06:15
Joined
Oct 18, 2019
Messages
72
Oops, sorry, I just noticed you provided a link.

I would rather have the code in Access if possible because I don't want to change the Excel form (Workbook used as a form). It has been in use for a long time and getting everyone to switch to a new one would be difficult... but not impossible.


Gale
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:15
Joined
Oct 29, 2018
Messages
21,357
Oops, sorry, I just noticed you provided a link.

I would rather have the code in Access if possible because I don't want to change the Excel form (Workbook used as a form). It has been in use for a long time and getting everyone to switch to a new one would be difficult... but not impossible.

Gale
Hi Gale. Yes, everything will be controlled from within Access. So, the code will be in Access. The Excel file doesn't even have to be open to grab data from it. It just has to be accessible from Access.
 

GaleT

Registered User.
Local time
Today, 06:15
Joined
Oct 18, 2019
Messages
72
LOL... I apologize, our messages are slightly out of synch, I appreciate your quick response.

"No. I am saying you can use Excel Automation to "grab" some data from an Excel file from within Access."
Thank you for the clarification... I understand now and that is exactly what I want to do. I'm off to read the link you provided... I did miss it.


Gale
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:15
Joined
Oct 29, 2018
Messages
21,357
Hi Gale. Just in case it helps, I just created the following function for you.
Code:
Public Function GetExcelData(FilePath As String, _
    SheetName As String, CellName As String) As Variant
'thedbguy@gmail.com
'11/13/2019

Dim xl As Object
Dim wb As Object
Dim ws As Object

Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Open(FilePath)
Set ws = wb.Sheets(SheetName)

GetExcelData = ws.Range(CellName)

wb.Close
xl.Quit

Set ws = Nothing
Set wb = Nothing
Set xl = Nothing

End Function
Let me know how it goes...
 

GaleT

Registered User.
Local time
Today, 06:15
Joined
Oct 18, 2019
Messages
72
Yes, this is very helpful and I really appreciate it :)
I will let you know how it goes...
Gale
 

GaleT

Registered User.
Local time
Today, 06:15
Joined
Oct 18, 2019
Messages
72
Your function worked great. I wrote the following to test it... I know this isn't anything new to you but I'm posting it in case it might be useful to someone else reading this thread.

Code:
Private Sub btnImportShortForm_Click()
'This sub calls the GetExcelData function to retrieve a value from an Excel Workbook

Dim FilePath As String, SheetName As String, CellName As String
Dim CellVal As Variant

FilePath = "S:\2019\AccessTestFolder\AZ19S0234 ShortForm.xlsx"
SheetName = "Sheet1"
CellName = "A1"

CellVal = GetExcelData(FilePath, SheetName, CellName)

MsgBox CellVal

End Sub
Again, what I am about to write isn't news to you but I want to complete the thread properly. This function works great for single data retrievals but because it opens and closes the file each time it is slow when multiple retrievals are required. I need to retrieve 11 figures each time the routine is executed so I am combining your example with arrays to address this in a slightly different way. Of course it isn't quite working yet but I will open another thread to ask for help. Thanks again for your help, your example answered more questions than I knew to ask :)
Gale
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:15
Joined
Oct 29, 2018
Messages
21,357
Your function worked great. I wrote the following to test it... I know this isn't anything new to you but I'm posting it in case it might be useful to someone else reading this thread.

Code:
Private Sub btnImportShortForm_Click()
'This sub calls the GetExcelData function to retrieve a value from an Excel Workbook

Dim FilePath As String, SheetName As String, CellName As String
Dim CellVal As Variant

FilePath = "S:\2019\AccessTestFolder\AZ19S0234 ShortForm.xlsx"
SheetName = "Sheet1"
CellName = "A1"

CellVal = GetExcelData(FilePath, SheetName, CellName)

MsgBox CellVal

End Sub
Again, what I am about to write isn't news to you but I want to complete the thread properly. This function works great for single data retrievals but because it opens and closes the file each time it is slow when multiple retrievals are required. I need to retrieve 11 figures each time the routine is executed so I am combining your example with arrays to address this in a slightly different way. Of course it isn't quite working yet but I will open another thread to ask for help. Thanks again for your help, your example answered more questions than I knew to ask :)
Gale
Hi Gale. Glad to hear the sample function gave you a good start. I'll be looking for your other thread in case there's something else I could contribute. Good luck with your project.
 

Users who are viewing this thread

Top Bottom