VBA, import file via dialog box to workbook

J

jazzsam

New Member
Joined
Feb 18, 2013
Messages
9
Hi all,

Forgive me if this has already been answered, ive been looking for a while now and not found an answer that works for me.

Im trying to use a command box that will open a file (allfiles is what im looking for) and import it from a network drive into a worksheet in the same excel file to a worksheet of my choosing. The file name will change every time so the dialog box is a must. also being able to set where the dialog box starts looking would be a great time saver e.g G:\folder\folder

the file will need to be standard fixed width and be in the same location (A1;A1 in worksheet 2) each time as the info will then need to be pulled out somewhere else (i can do that bit). ive been playing around with other peoples VBA code and just quite cant get there by myself.

FYI im a little bit of a noob when it comes to VBA

Cheers
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Have a look at Application.FileDialog (msoFileDialogFilePicker). You can define the initial folder or file name. Combine this with code generated by the macro recorder of you doing a File - Open, followed by copy and paste sheet or cells to the destination sheet and with a bit of editing you are almost there.
 
Upvote 0
Thanks John, thats essentially what i ended up with. i just wrote a half dozen different macros then mad a macro to run then all, not the most efficient way but it worked in the end.
 
Upvote 0


I amreviving this topic because I have pretty much the same issue.





I havefound a first written macro that helps me open a dialog box





Code: 
[/FONT][/COLOR][SIZE=3][COLOR=#000000][FONT=Calibri]Sub GettingFile()[/FONT][/COLOR][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][SIZE=3][COLOR=#000000]Dim SelectedFile As String[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][SIZE=3][COLOR=#000000]Dim wbSource As Workbook[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][SIZE=3][COLOR=#000000]WithApplication.fileDialog(msoFileDialogFilePicker)[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        [/COLOR][/SIZE][SIZE=3][COLOR=#000000].AllowMultiSelect = False[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        [/COLOR][/SIZE][SIZE=3][COLOR=#000000].Title = "Select file"[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        [/COLOR][/SIZE][SIZE=3][COLOR=#000000].ButtonName = "Confirm"[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        [/COLOR][/SIZE][SIZE=3][COLOR=#000000].InitialFileName = "O:\FileTransfers\"[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            [/COLOR][/SIZE][SIZE=3][COLOR=#000000]If .Show = -1 Then[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            [/COLOR][/SIZE][SIZE=3][COLOR=#000000]'ok clicked[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            [/COLOR][/SIZE][SIZE=3][COLOR=#000000]SelectedFile = .SelectedItems(1)[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            [/COLOR][/SIZE][SIZE=3][COLOR=#000000]MsgBox SelectedFile[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            [/COLOR][/SIZE][SIZE=3][COLOR=#000000]Else[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            [/COLOR][/SIZE][SIZE=3][COLOR=#000000]'cancel click[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            [/COLOR][/SIZE][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]     [/COLOR][/SIZE][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT][COLOR=#222222][FONT="Verdana"]





Then I recorded a macro to import the files, the code Iget is :





Code: 
[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]Sub ImportFile()[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]    WithActiveSheet.QueryTables.Add(Connection:= _[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       "TEXT;O:\Exp\File Transfers\F\2019JR.txt", _[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       Destination:=Range("$A$1"))[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]        .Name ="2019JR"[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]        .FieldNames= True[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]        .RowNumbers= False[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       .FillAdjacentFormulas = False[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       .PreserveFormatting = True[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       .RefreshOnFileOpen = False[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       .RefreshStyle = xlInsertDeleteCells[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       .SavePassword = False[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]        .SaveData =True[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       .AdjustColumnWidth = True[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       .RefreshPeriod = 0[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       .TextFilePromptOnRefresh = False[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       .TextFilePlatform = 850[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       .TextFileStartRow = 1[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       .TextFileParseType = xlDelimited[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       .TextFileTextQualifier = xlTextQualifierDoubleQuote[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       .TextFileConsecutiveDelimiter = False[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       .TextFileTabDelimiter = True[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       .TextFileSemicolonDelimiter = True[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       .TextFileCommaDelimiter = False[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       .TextFileSpaceDelimiter = False[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,1, 1, 1, 1, 1, 1, 1, 1, _[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]        1, 1, 1, 1,1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       .TextFileTrailingMinusNumbers = True[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]        .RefreshBackgroundQuery:=False[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]    End With[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]End Sub[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"] [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]





I unserstand the key is to link the first macro with the secondone through the line:





Code: 
[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]With ActiveSheet.QueryTables.Add(Connection:= _[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       "TEXT;O:\Exp\File Transfers\F\2019JR.txt", _[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT="Verdana"]       Destination:=Range("$A$1"))








UnfortunatelyI don’t know how to do


Thanks foryour help
 
Upvote 0
Code: 
With ActiveSheet.QueryTables.Add(Connection:= _
       "TEXT;" & SelectedFile, _
       Destination:=Range("$A$1"))
 
Upvote 0
Thanks for your help… unfortunately it still does not seem to work. The dialog box opens but it still does not import the data on the excel file.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
VBA-Photos to be imported from Sharepoint Site rather than local drive
Replies
1
Views
158
Micron
M
E
  • Question
Split Workbook VBA w/Default Value for Sensitivity Labels
Replies
1
Views
268
Jeffrey Mahoney
Jeffrey Mahoney
S
  • Question
VBA import of an IQY file without prompt
Replies
4
Views
347
John_w
J
M
  • Question
Excel VBA Save PDF within workbook folder and send as email attachment
Replies
1
Views
203
rpaulson
rpaulson
D
  • Solved
Export and name sheets within a workbook to a text file in same directory as workbook
Replies
3
Views
502
rlv01
R

Forum statistics

Threads
1,214,649
Messages
6,120,732
Members
448,987
Latest member
marion_davis

Share this page

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