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

[ShovelingFishHeads]

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

 

[Trevor G]

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>

 

[ShovelingFishHeads]

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.

 

[Trevor G]

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-comfficeffice" /><o></o>
<o> </o>
‘You would need to add your code from here<o></o>
‘But check to see what happens as I don’t have your files<o></o>
‘Are they laid out the same?<o></o>
With ActiveSheet.Range(“A1”).Select<o></o>
.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></o>

 

[ShovelingFishHeads]

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?

 

[ShovelingFishHeads]

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-comfficeffice" /><o></o>
<o></o>
With ActiveSheet.Range(“A1”).Select<o></o>
.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

 

[ShovelingFishHeads]

Dim retval AsBoolean

this line gave me a syntax error; tried it in 2003 and 2010 versions of excel.

wondering why

 

[Trevor G]

Add a space between As Boolean