I have code that I have been using to import excel files into my db (curtesy to TheDBGuy), but the time has already come that I need to get even more complicated with it for a better user experience. First few people I have let try the process, have royally messed it up.
The code that I have been using is:
This works like a charm, but requires the excel file to be cleaned up a bit before they can imported.
I am wanting the code to do all the cleaning up itself to avoid the human error.
I know the concept, but not how to write it. I would open excel as an object then loop through the rows defining what needs deleted and what needs kept.
I have found things like https://stackoverflow.com/questions/45714842/access-vba-to-open-edit-and-save-excel-docm but this (to me) is pretty complicated and I don't fully understand what all is happening with that code.
Long story short, I am trying to open excel as an object, delete rows, lets say 1-16 (and a floating image as all the files have one), after the rows are deleted, a specific row should now be row 1 (my column headers in Access) then loop through all remaining rows to find a specific word in a certain column (in my case the word would be something like grand total in say column G, then select that row and delete it and everything below it. That would allow the rest of my code to run and import the file with no human interaction on the excel file (minus dropping it on their desktop from their email or something).
I have dealt with looping once or twice, but neither required something so specific.
The code that I have been using is:
Code:
Public Function ImportXLBF() As Boolean
'thedbguy@gmail.com
'10/9/2020
Dim fd As Object
Dim strFile As String
Set fd = Application.FileDialog(3)
With fd
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel Files", "*.xls*"
If .Show Then
strFile = .SelectedItems(1)
End If
End With
If strFile = "" Then
ImportXL = False
Else
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblTempBrightFocus", strFile, True
ImportXL = True
End If
Set fd = Nothing
End Function
I am wanting the code to do all the cleaning up itself to avoid the human error.
I know the concept, but not how to write it. I would open excel as an object then loop through the rows defining what needs deleted and what needs kept.
I have found things like https://stackoverflow.com/questions/45714842/access-vba-to-open-edit-and-save-excel-docm but this (to me) is pretty complicated and I don't fully understand what all is happening with that code.
Long story short, I am trying to open excel as an object, delete rows, lets say 1-16 (and a floating image as all the files have one), after the rows are deleted, a specific row should now be row 1 (my column headers in Access) then loop through all remaining rows to find a specific word in a certain column (in my case the word would be something like grand total in say column G, then select that row and delete it and everything below it. That would allow the rest of my code to run and import the file with no human interaction on the excel file (minus dropping it on their desktop from their email or something).
I have dealt with looping once or twice, but neither required something so specific.