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
 
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

A
  • Question Question
Saving dialog box hanging after running macro
2
Replies
16
Views
634
Alex Blakenburg
A
L
  • Solved
Attempting importing sheet from data file into destination file.
2
Replies
16
Views
325
Alex Blakenburg
A
E
  • Solved
Force Excel 2019 VBA to import multiple txt file to existing sheet & not seperate wk.book / wk.sheets
Replies
1
Views
734
Domenic
D
yearego021
  • Solved
VBA to Save as PDF broken with unwanted deleting of tabs.
Replies
7
Views
244
Artik
Artik
C
  • Solved
Converting .txt file to .csv, splitting files and adding rows
2
Replies
10
Views
181
HongRu
HongRu

Forum statistics

Threads
1,226,849
Messages
6,193,330
Members
453,790
Latest member
yassinosnoo1

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