XLS Data Import -- Would like to use the "Browse Folder" (vs. "strPath = "C: ....)"

[skydivetom]

Welshgasman -- copy all, thank you for the feedback. I'll do some additional research.



In my original post, I indicated "When clicking on the list menu item (in form), the two XLS sheets are properly imported correctly." However, I can see the requirement for *MULTIPLE XLS* (at once) was not emphasized enough. I apologize for any confusion.

[Welshgasman]

Ok, I have worked out one method. The experts may have a better method.

I created a public variable that would be a string array

Public strFiles() As String

Then in the function I stored each selected item to the array.? You need to Dim intCount as Integer. Will also likely need to clear the array each time?

Debug.Print .SelectedItems.Count
ReDim strFiles(.SelectedItems.Count)

For IntCount = 1 To .SelectedItems.Count
    strFiles(IntCount) = .SelectedItems(IntCount)
    Debug.Print .SelectedItems(IntCount)
Next

Now when you return from the function ALL the filenames selected are available and you just need to loop through the strFiles array

Sub testUserPickFunction()
Dim tt As String
Dim IntCount As Integer

tt = UserPick1File("c:\temp\")

For IntCount = 1 To UBound(strFiles)
    Debug.Print "File " & IntCount & " is " & strFiles(IntCount)
Next
End Sub

[skydivetom]

Thank you... I'll try to figure out which goes into the ListboxAfterClick and which ones goes into the module. Not entirely clear from the post, but I'll dabble w/ it.

Again, thanks!

[Welshgasman]

That last code block is just to check my code works. You do not need that.?
The second code block goes into Ranman's function so you have all the filenames. That code goes AFTER you have selected the files and before it returns the first filename.

You then use those names in the array where you need them?

[skydivetom]

Welshgasman:

Thank you for being patient... still learning some new aspects here.

Please see attached zip file. It includes the following:
- ImportRoutine v03
- Subfolder with 2 sample XLS.


Process:
1. Upon opening "frmImportRoutineBrowseDialogue", I want to click on the listbox's menu item which then open the Browse dialogue box
2. Once I select the folder/subfolder, I want to be able to loop through it and automatically load all existing XLS (or I pick the 2 XLS) into the DB
3. The spreadsheets needs to be loaded in the same format as "tbl_Source1" and "tbl_Source2" (where the files names become the tables name; if tab names will become table names, that's ok too).

At this moment, I'm not entirely clear how to bring all the various code elements together. If all possible, could you please review the attached DB and re-post it once necessary corrections have been made?

Thank you,
Tom

[Welshgasman]

Ok,
I have had a go. No idea what you are doing with the worksheets collection.
The code as it is failed on Sheet2 as file1 only has 1 worksheet. So you need to decide again what you want. You are mixing and matching without any regard to the code and what it is doing.?

This is just going to cause you more issues as time goes by. I know you are on a timeline, but you need to decide what it is you want to the code to do.

As it stands, if you get rid of the collection (jeez I have never used a collection in my life, talk about jumping in at the deep end. ) I believe the code as it is will populate the tables.
However that is now assuming that first file is 1, second 2 and so on? If the tables are not named correctly then the order will not be correct and you could be putting 32 into table 1?

So you have some decisions to make I believe, before trying to go any further.?

I've also used the first form as there was nothing in the dialogue form to use?, plus I do not think the afterupdate event is the correct one as nothing gets updated.? I used another event, either Click or Double click. Again, I would have used a button not a list box, but that is just me.

So perhaps decide what you want, bearing in mind the caveats I mentioned above and then we can progress.

[skydivetom]

Welshgasman:

First of all, allow me to thank you for the continued assistance and patience. At the same time, I really do not understand why my requirements are so difficult to follow.

Allow me to recap:
- I have two (2) spreadsheets.
- File names are: tbl_Source1; tbl_Source2
- Both spreadsheets are stored in a folder/subfolder
- Each of the two (2) XLS has one (1) tab!!!

Access Form:
- I have one (1) form "frmImportRoutine" in the Access database
- The form includes one (1) listbox with one (1) menu item
- In the VBA, line 55, I have the following command: strPath = "C:\Users\...\...\Import Routine\SourceData\2 Files"
- Once I click on the menu item, the current AfterUpdate routine imports data from both spreadsheets/tab into two (2) separate tables.

... that's it for the existing process! Again, I fail to see the difficulty, e.g., "So you need to decide again what you want." or "So you have some decisions to make I believe, before trying to go any further."


Problem w/ the existing solution:
- Again, currently my strPath is hard-coded into the VBA. That's ok when I'm working with the file on my home/work computer.
- Thus, when sharing the database, this is causing an issue. Naturally, the full database includes more than just one stand-alone form... but that's not the point.
- So, again, when sharing the DB, I have to tell users to go into the VBA and change the file path in line 55.
- And this is where the "Browse Dialogue" should come in.

Here's what I wanted to achieve:
- Replace either line 55 or appropriate section of the Import routine so that a user can browse to the folder location wherever they stored their XLS
- Once they browsed to the folder, they should be able to select two (2) or one-hundred (100) XLS and press "Import".
- Then, just like right now, I can import 100 XLS into 100 tables within a few seconds.

I hope the latter clarified it. If not, I'll mark this post as "Resolved" (without being resolved).

Thank you,
Tom

P.S. Attached is the DB with the single form which currently imports multiple XLS all at once. I just want to switch from the hard coded file path to the Browse dialogue.

[accesstos]

Tom,

Just a quick and dirty fix...

Replace the code of the form frmImportRoutine with the code below:

Option Compare Database

Private Sub Form_Open(Cancel As Integer)

    'Routine for Importing Data
    Me.Listbox_ImportData.RowSource = "Import Source Data"

End Sub

Private Sub Listbox_ImportData_AfterUpdate()

    'Routine for Importing Data
    If Me.Listbox_ImportData = "Import Source Data" Then

        'Declare variables for import routine
        Dim blnHasFieldNames As Boolean
        Dim blnEXCEL As Boolean
        Dim blnReadOnly As Boolean
        Dim intWorkbookCounter As Integer
        Dim lngCount As Long
        Dim objExcel As Object
        Dim objWorkbook As Object
        Dim colWorksheets As Collection
        Dim strPath As String
        Dim strFile As String

        'Execute DROP TABLE command for all tables (source files) with a "tbl_" prefix
        Dim tdf As TableDef
        Dim strSQL As String
        Dim db As DAO.Database
        Set db = CurrentDb

        For Each tdf In db.TableDefs
            If Left(tdf.Name, 4) = "tbl_" Then
                strSQL = "DELETE * FROM " & tdf.Name
                Debug.Print strSQL
                db.Execute strSQL
            End If
        Next

        'Establish an EXCEL application object
        On Error Resume Next
        Set objExcel = GetObject(, "Excel.Application")
        If Err.Number <> 0 Then
            Set objExcel = CreateObject("Excel.Application")
            blnEXCEL = True
        End If
        Err.Clear
        On Error GoTo 0

        'Change the next line to FALSE in the event the first row (in EXCEL file) does NOT have field names
        blnHasFieldNames = True

        'Set the file path
        'strPath = "C:\Users\...\...\Import Routine\SourceData\2 Files\"
        strPath = GetPath()
        If Len(strPath) Then
            If Right(strPath, 1) <> "\" Then
                strPath = strPath & "\"
            End If
            'Open EXCEL files in read-only mode
            blnReadOnly = True

            strFile = Dir(strPath & "*.xlsx")
            intWorkbookCounter = 0

            Do While strFile <> ""

                intWorkbookCounter = intWorkbookCounter + 1

                Set colWorksheets = New Collection

                Set objWorkbook = objExcel.Workbooks.Open(strPath & strFile, , blnReadOnly, , strPassword)

                For lngCount = 1 To objWorkbook.Worksheets.Count
                    colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
                Next lngCount

                'Close the EXCEL files without saving the file, and clean up the EXCEL objects
                objWorkbook.Close False
                Set objWorkbook = Nothing

                'Import the data from each worksheet into separate tables
                For lngCount = colWorksheets.Count To 1 Step -1
                    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
                                              "tbl_" & colWorksheets(lngCount), _
                                              strPath & strFile, blnHasFieldNames, _
                                              colWorksheets(lngCount) & "$"

                Next lngCount

                'Delete the collection
                Set colWorksheets = Nothing
                strFile = Dir()

            Loop

            MsgBox intWorkbookCounter & " source files were successfully imported.", vbInformation, "Import Status"
        End If
        If blnEXCEL = True Then objExcel.Quit
        Set objExcel = Nothing

    End If

End Sub

Private Function GetPath() As String
    'Browse for a folder...
    Dim FD As FileDialog

    Set FD = Application.FileDialog(msoFileDialogFolderPicker)
    With FD
        .AllowMultiSelect = False
        .Title = "Select a folder"
        .InitialFileName = CurrentProject.Path & "\"
        If .Show Then
            GetPath = .SelectedItems(1)
        End If
    End With
    Set FD = Nothing
End Function

(My changes in blue)

I hope it works.
Question: Is there any case to import files with multiple worksheets?

Ciao!

[moke123]

deleted as accesstos got it coverred

[accesstos]

deleted as accesstos got it coverred

This time I was quicker.

(and more Dirty)

[skydivetom]

Happy Monday Morning, John:

You came through for me again!

Instead of sending me on a "wild goose chase", your solution was simple, easy, AND elegant!

PERFECT YET AGAIN!!! Thank you for solving it so promptly.

Cheers,
Tom

[Welshgasman]

Happy Monday Morning, John:

You came through for me again!

Instead of sending me on a "wild goose chase", your solution was simple, easy, AND elegant!

PERFECT YET AGAIN!!! Thank you for solving it so promptly.

Cheers,
Tom

You were the one choosing to select multiple files via Ranman's code?

As mentioned you should have used Folderpicker
https://wellsr.com/vba/2016/excel/vb...gFolderPicker/


I'll bow out of any future discussions, as I would not want to lead you on a wild goose chase again.

Such a shame that the second post in this thread posted by me, showed you EXACTLY how to do that.???

[skydivetom]

Welshgasman -- sorry for the confusion. I thank you anyways.

Cheers,
Tom

[skydivetom]

accesstos, moke123:

I posted a related (but yet different) topic at the following: https://www.accessforums.net/showthread.php?t=83287

I always welcome your ideas/recommendations. Thank you.