How can I integrate the manual opening of the Import Text process into a macro?

ShovelingFishHeads

New Member
Joined
Jun 4, 2012
Messages
5
Hi everyone!

How can I integrate the manual opening of the "Import Text Data" process into a macro?

I have a client that wants a macro that automates the process of importing data from a text file into an excel worksheet, but they are adament that the user manually opens the text file (searching around among directories) to find the specific file. Actual importation once the file is selected is to be automated.

This sounds a little strange and overly compledated I know, but the client doesn't want to do it in a simpler way.

Can I start the macro with the openning of the directory list dialogue and then have it continue once one file is double clicked? how would that look in code?

code i am using to import looks like this:


'''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''
'
'Get file data imported
'Write formulas in the next macro
'
'''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''
Sub OpenTextFile()
'
' OpenTextFile Macro
'
'
With ActiveSheet.QueryTables.Add(Connection:="TEXT;G:\list.BSC", _
Destination:=Range("$A$1"))
.Name = "list"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(28, 4, 30, 4, 9, 16, 42, 3, 20, 18, 12, 30, 5, 11)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Any help is greatly appreciated. T H A N K S!!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Welcome to the Forum,

You can incorporate using the open text dialog to your code so that the user can pick the file.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> openDial()<br><SPAN style="color:#00007F">Dim</SPAN> retval <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>retval = Application.Dialogs(xlDialogOpen).Show("*.txt")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Thanks Trevor G,

I appreciate it.

The code you provided has me a bit mystified. Could you elaborate on how it works and how it would be used in my code? I am certain many will benefit from this.
 
Upvote 0
All this code does is use a Boolean Event, which means True or False. Then it is using the application command to use the File Open Dialog box, you then find your file and then your code would do the rest.

Sub OpenTextFile()
'
' OpenTextFile Macro
Dim retval As Boolean
retval = Application.Dialogs(xlDialogOpen).Show("*.txt")<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>

<o:p> </o:p>
‘You would need to add your code from here<o:p></o:p>
‘But check to see what happens as I don’t have your files<o:p></o:p>
‘Are they laid out the same?<o:p></o:p>
With ActiveSheet.Range(“A1”).Select<o:p></o:p>
.Name = "list"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(28, 4, 30, 4, 9, 16, 42, 3, 20, 18, 12, 30, 5, 11)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
<o:p></o:p>
 
Upvote 0
thanks again for getting back to this and continuing to provide guidence.

I'll give your code a try and get back here about it.

one question: where you say,

Are they laid out the same?

to what are you refering?
 
Upvote 0
I'll assume that the code should read like this to accomplish my objective:

Sub OpenTextFile()
'
' OpenTextFile Macro
Dim retval AsBoolean
retval = Application.Dialogs(xlDialogOpen).Show("*.txt")<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>

<o:p></o:p>
With ActiveSheet.Range(“A1”).Select<o:p></o:p>
.Name = "list"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(28, 4, 30, 4, 9, 16, 42, 3, 20, 18, 12, 30, 5, 11)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
 
Upvote 0
Add a space between As Boolean
 
Upvote 0

Forum statistics

Threads
1,211,792
Messages
6,104,000
Members
447,890
Latest member
CADim_JParker

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