User Chooses .xlsx file to run saved import

ARW17

Board Regular
Joined
Oct 31, 2016
Messages
109
I have a FileDialog step where the user selects the file they want to import. The files they select have similar names, but the date in the name of the file changes.

I have successfully done this with importing .csv files, but there aren't any Advanced settings to save an import specification for importing from Excel?

Can you please provide a suggestion for how I can use VBA to have the user select a file (with a changing name) to import and append to an existing table? Here's an example of how I'm doing this with a .csv file.

Function Get_File_WFLO()

'Declare a variable to contain FileDialog
Dim sMyPath As FileDialog
'Declare a variable to contain the path
Dim wPath As Variant


DoCmd.SetWarnings False
DoCmd.Hourglass True
' Set up the File Dialog.

Set sMyPath = Application.FileDialog(msoFileDialogFilePicker)

With sMyPath

' Do not allow users to make multiple selections in dialog box
.AllowMultiSelect = False

' Set the title of the dialog box.
.Title = "Select your Archived WFLO File"

' Clear out the current filters, and add your own.
.Filters.Clear
.Filters.Add "wflo *", "*.csv"

'Go to the Archive folder.
.initialfilename = "K:\Customer Service Capacity Queue Data Tool\Queue Data Inputs\Archive"

'Set the text of the button Caption
.ButtonName = "Get WFLO File"

'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then
For Each wPath In .SelectedItems

'MsgBox wPath

'Import into WFLO table
DoCmd.OpenQuery "WFLO_DELETE"
DoCmd.TransferText acImportDelim, "WFLO_import_spec_date", "WFLO", wPath, False

Next wPath
'Gut check the upload numbers.
Dim MsgW, StyleW, TitleW, ResponseW
MsgW = "WORKFLOW file was uploaded and contained " & DCount("*", "WFLO") & " records. A typical workday usually has around 30,000."
StyleW = vbOKOnly + vbInformation + vbDefaultButton1
TitleW = "IC1 File Uploads"
ResponseW = MsgBox(MsgW, StyleW, TitleW)

Else
'Show if Canceled is selected in a message box
wPath = "No File Selected to Import."
MsgBox wPath
End If

End With
DoCmd.SetWarnings True
DoCmd.Hourglass False

End Function
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
I guess you missed or forgot the request to post code within code tags in your similar thread here (which you didn't mark solved or respond to the posts you received)

Is this thread a different issue, because that code looks quite similar?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top