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?
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.
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.
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.
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.
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
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
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.