Access to search multiple Excel documents? (1 Viewer)

[KV_access]

Hello,

So to start off, I know enough Excel to be little bit dangerous but I know practically nothing about Access. I am trying to learn Access in hope of creating a database which will "search/import" information from multiple excel documents. To lay the groundwork:

I will have a separate excel document (all located in one folder) for each year which contains information like:

Date, Job #, Personnel, Job Description, Etc... (up to roughly 20 columns)

All this information is located in a table in the second named sheet of the excel document and is formatted the same for each and every year. Each table could be upwards of multiple tens of thousands of lines long.

My hope is to create an access database which would allow me to search the multiple excel documents and import the information (preferably dynamically) to one Access table. The search/import would need the ability to select multiple criteria, such as but not limited to, all dates in between 12/01/2019 and 2/29/2020 and contain the Job # 411111 with the remaining columns being "wildcarded".

I am not sure if I am clear in what I am asking to accomplish and to be honest I am lost on where to begin. Any help, guidance, or similar posts I could read would be greatly appreciated.

Thank you!
KV

 

[theDBguy]

Hi KV. Welcome to AWF!

For starters, you might try creating a linked table in Access for all your Excel files. It would probably make it easier to do a search if everything is within Access. Just a thought...

 

[The_Doc_Man]

Concur with theDBguy. Here is my "value added"

Import or link to data in an Excel workbook - Microsoft Support

You can import Excel data into Access desktop databases or link to an Excel file, which results in a linked table.

support.office.com

This link has TWO headings - importing and linking. You have to scroll down to find the Linking section, but it gives you a lot of info.

 

[bastanu]

And once linked create an union query to have all records in one place to do your search.

Cheers,
Vlad

 

[vba_php]

KV,

the other things you have available to you in code are things like the ADO method, which is a windows object library. ADO = active data objects, I believe. you can start a data stream through that. it is listed in your VBA references, if you simply search for it through the alphabetical list.

you should also know that the Ctrl+F keyboard shortcut, when trying to do it while recording with the macro recorder in excel itself does NOT work. and thus, you can't perform it through access either even if you created all the inter-office VBA objects in an attempt to do so.

 

[KV_access]

Hi KV. Welcome to AWF!

For starters, you might try creating a linked table in Access for all your Excel files. It would probably make it easier to do a search if everything is within Access. Just a thought...

Concur with theDBguy. Here is my "value added"

Import or link to data in an Excel workbook - Microsoft Support

You can import Excel data into Access desktop databases or link to an Excel file, which results in a linked table.

support.office.com

This link has TWO headings - importing and linking. You have to scroll down to find the Linking section, but it gives you a lot of info.

And once linked create an union query to have all records in one place to do your search.

Cheers,
Vlad

Thank you for the help. I have managed to create (using others code, forums, and your help) a macro which looks through a folder and links all excel files with a sheet named "Database" in a access link table. I believe this is a start to what I hope to accomplish, but I am not sure what the best path to take from here is.

Here is the code:

Sub Link_From_Excel()

' This subprocedure determines whether
' any Excel files exist in the folder stored
' in the strcPath constant; if there are,
' this subprocedure links the data in the
' Excel files.


' Store paths:
Const strcPath As String = _
"C:\Users\XXXXXXX\Desktop\Access Link Test"

' Store the name of the table into which
' the data will be imported
Const strcTableName As String = "LinkTable"

Dim strPath As String
Dim strNewPath As String
Dim strFile As String
Dim strFileList() As String
Dim intFile As Integer
Dim strFullPath As String


' See if path constant ends in a backslash:
If Right(strcPath, 1) = "\" Then
strPath = strcPath
Else
strPath = strcPath & "\"
End If


' Loop through the Excel files in the folder
' (if any) and build file list:
strFile = Dir(strPath & "*.xlsm")
While strFile <> ""
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend


' See if any files were found:
If intFile = 0 Then

MsgBox strcPath & vbNewLine & vbNewLine _
& "The above directory contains no Excel " _
& "files.", _
vbExclamation + vbOKOnly, "Program Finished"

GoTo Exit_Link_From_Excel

End If


' Loop through the list of files:
For intFile = 1 To UBound(strFileList)

' Initialise paths:
strFullPath = strPath & strFileList(intFile)

' Link into Access:
DoCmd.TransferSpreadsheet acLink, _
acSpreadsheetTypeExcel97, strcTableName, _
strFullPath, True, "Database!"

Next

MsgBox UBound(strFileList) & " file(s) were Linked", _
vbOKOnly + vbInformation, "Program Finished"

Exit_Link_From_Excel:

Exit Sub

End Sub

A couple things I am stuck on. The macro creates LinkTable1, LinkTable2, etc... for each excel file. Would it be possible for each of the link tables to have the same name as Excel Document it links to, or would it be best to somehow merge the link tables into a "master" linked table? I am use to just using table filters to filter out the information I care about and then run figures on the information displayed in the table. Example would be filtering out only March of last year and then adding total # of jobs performed, or average number of personnel per job, or something like that.

So would my best step forward be to just you queries to do this? Or somehow merge into a single large "link" table and just filter? Sorry, I am still really new to access. Thanks for the help.

 

[KV_access]

KV,

the other things you have available to you in code are things like the ADO method, which is a windows object library. ADO = active data objects, I believe. you can start a data stream through that. it is listed in your VBA references, if you simply search for it through the alphabetical list.

you should also know that the Ctrl+F keyboard shortcut, when trying to do it while recording with the macro recorder in excel itself does NOT work. and thus, you can't perform it through access either even if you created all the inter-office VBA objects in an attempt to do so.

Thank you for the help, I will have to check more into the ADO method, but I ended up using accesses linked tables. I have posted a couple issues I am having if you happen to have any guidance on them. Thanks again!

 

[theDBguy]

Thank you for the help. I have managed to create (using others code, forums, and your help) a macro which looks through a folder and links all excel files with a sheet named "Database" in a access link table. I believe this is a start to what I hope to accomplish, but I am not sure what the best path to take from here is.

Here is the code:

Sub Link_From_Excel()

' This subprocedure determines whether
' any Excel files exist in the folder stored
' in the strcPath constant; if there are,
' this subprocedure links the data in the
' Excel files.


' Store paths:
Const strcPath As String = _
"C:\Users\XXXXXXX\Desktop\Access Link Test"

' Store the name of the table into which
' the data will be imported
Const strcTableName As String = "LinkTable"

Dim strPath As String
Dim strNewPath As String
Dim strFile As String
Dim strFileList() As String
Dim intFile As Integer
Dim strFullPath As String


' See if path constant ends in a backslash:
If Right(strcPath, 1) = "\" Then
strPath = strcPath
Else
strPath = strcPath & "\"
End If


' Loop through the Excel files in the folder
' (if any) and build file list:
strFile = Dir(strPath & "*.xlsm")
While strFile <> ""
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend


' See if any files were found:
If intFile = 0 Then

MsgBox strcPath & vbNewLine & vbNewLine _
& "The above directory contains no Excel " _
& "files.", _
vbExclamation + vbOKOnly, "Program Finished"

GoTo Exit_Link_From_Excel

End If


' Loop through the list of files:
For intFile = 1 To UBound(strFileList)

' Initialise paths:
strFullPath = strPath & strFileList(intFile)

' Link into Access:
DoCmd.TransferSpreadsheet acLink, _
acSpreadsheetTypeExcel97, strcTableName, _
strFullPath, True, "Database!"

Next

MsgBox UBound(strFileList) & " file(s) were Linked", _
vbOKOnly + vbInformation, "Program Finished"

Exit_Link_From_Excel:

Exit Sub

End Sub

A couple things I am stuck on. The macro creates LinkTable1, LinkTable2, etc... for each excel file. Would it be possible for each of the link tables to have the same name as Excel Document it links to, or would it be best to somehow merge the link tables into a "master" linked table? I am use to just using table filters to filter out the information I care about and then run figures on the information displayed in the table. Example would be filtering out only March of last year and then adding total # of jobs performed, or average number of personnel per job, or something like that.

So would my best step forward be to just you queries to do this? Or somehow merge into a single large "link" table and just filter? Sorry, I am still really new to access. Thanks for the help.

Hi. If you want a different name for your linked tables, you may have to modify your code to specify that (I haven't reviewed the code yet). Then, the next step could be to create a form as a search form with code to go through all the linked tables and search them.

 

[vba_php]

Thank you for the help, I will have to check more into the ADO method, but I ended up using accesses linked tables. I have posted a couple issues I am having if you happen to have any guidance on them. Thanks again!

no problem at all. I tend to take the more difficult routes and people like Richard tend to take the easier routes when giving guidance