Solved Looping Multi-Selected Files (1 Viewer)

pooldead · Nov 5, 2019

I have the following code setup to import a file, eventually files, and move the selected file(s) to a new folder. Everything works great. I am trying to figure out how I can apply the "TransferSpreadsheet" and "FSO_File_Move" pieces to each file selected. I've thought about For-Next statements, but they haven't worked so far.

Code:

                Set importFile = Application.FileDialog(msoFileDialogFilePicker)
                    With importFile
                        .Title = "Choose which file(s) to import"
                        .AllowMultiSelect = False
                        If .Show = -1 Then
                            fileName = .SelectedItems(1)
                            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Transsend_M4", fileName, True
                            fileNameNew = Tools.GetFilenameFromPath(fileName)
                            FSO_File_Move fileNameNew, filePath, newFilePath
                        Else

theDBguy · Nov 5, 2019

You could try using a For Each loop. For instance:

Code:

For Each var In .SelectedItems
    'export data
    'move file

Next

pooldead · Nov 5, 2019

theDBguy said:
You could try using a For Each loop. For instance:

Code:

For Each var In .SelectedItems 'export data 'move file Next

Where would I put that? Before I assign .SelectedItems to the variable fileName or after?

And would I replace var with fileName in the code you provided?

theDBguy · Nov 5, 2019

pooldead said:
Where would I put that? Before I assign .SelectedItems to the variable fileName or after?

And would I replace var with fileName in the code you provided?

Hi. Maybe this would be clearer.

Code:

[B]Dim var As Variant[/B]

Set importFile = Application.FileDialog(msoFileDialogFilePicker)
    With importFile
        .Title = "Choose which file(s) to import"
        .AllowMultiSelect = [B]True[/B]
        If .Show = -1 Then
            For Each var In .SelectedItems
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Transsend_M4", [B]var[/B], True
                fileNameNew = Tools.GetFilenameFromPath(var)
                FSO_File_Move fileNameNew, filePath, newFilePath
            Next

         Else

(untested)
Hope that helps...

pooldead · Nov 5, 2019

Yup, that does help. I'll test it out and post an update. Thanks!

theDBguy · Nov 5, 2019

pooldead said:
Yup, that does help. I'll test it out and post an update. Thanks!

Oops, I forgot the Next line in the code.

PS. Okay, I added it above. Whew!

pooldead · Nov 5, 2019

Thanks theDBguy, that did the trick! I knew I was on the right track, but couldn't work out the syntax.

theDBguy · Nov 5, 2019

pooldead said:
Thanks theDBguy, that did the trick! I knew I was on the right track, but couldn't work out the syntax.

Hi. Congratulations! Glad to hear you got it to work. Good luck with your project.

Solved Looping Multi-Selected Files (1 Viewer)

pooldead

Registered User.

theDBguy

I’m here to help

pooldead

Registered User.

theDBguy

I’m here to help

pooldead

Registered User.

theDBguy

I’m here to help

pooldead

Registered User.

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread