I am working on creating a macro that takes text data and import it into an
existing worksheet. To do this, I normally using the Import Data wizard.
Since I do this several times a day, I am trying to create a macro that will
allow me to select the text file and select a cell placement for the data
once the text files is created. I have most of the code created, but cannot
get it to pause at the file open dialog box or the cell range box. Are there
specific codes I need to use to get this macro to do this? files into Excel
from Word several times a day.

With ActiveSheet.QueryTables.Add(Connection:="Text Files (*.txt), *.txt", _
Destination:=Range("A2"))
.Name = "subcount"
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Try this for getting the file name
Sub GetFile()
Dim File1 As String

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Text Files", "*.txt"
.Title = "Please Select Text File to Import"
If .Show Then
File1 = .SelectedItems(1)
MsgBox File1
Else
MsgBox "User Clicked Cancel"
End If
End With
End Sub

then in the activesheet.querytables.add, connection = File1

Charles Chickering

Miasha wrote:
I am working on creating a macro that takes text data and import it into an
existing worksheet. To do this, I normally using the Import Data wizard.
Since I do this several times a day, I am trying to create a macro that will
allow me to select the text file and select a cell placement for the data
once the text files is created. I have most of the code created, but cannot
get it to pause at the file open dialog box or the cell range box. Are there
specific codes I need to use to get this macro to do this? files into Excel
from Word several times a day.

With ActiveSheet.QueryTables.Add(Connection:="Text Files (*.txt), *.txt", _
Destination:=Range("A2"))
.Name = "subcount"
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Thanks very much. Just trying to figure out where in the code to place this
string. It seems like it should be placed after the With
ActiveSheet.QueryTables.Add... line. When I place it at the beginning, I get
a 'Method Range of object' error message.

"Die_Another_Day" wrote:

Try this for getting the file name
Sub GetFile()
Dim File1 As String

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Text Files", "*.txt"
.Title = "Please Select Text File to Import"
If .Show Then
File1 = .SelectedItems(1)
MsgBox File1
Else
MsgBox "User Clicked Cancel"
End If
End With
End Sub

then in the activesheet.querytables.add, connection = File1

Charles Chickering

Miasha wrote:
I am working on creating a macro that takes text data and import it into an
existing worksheet. To do this, I normally using the Import Data wizard.
Since I do this several times a day, I am trying to create a macro that will
allow me to select the text file and select a cell placement for the data
once the text files is created. I have most of the code created, but cannot
get it to pause at the file open dialog box or the cell range box. Are there
specific codes I need to use to get this macro to do this? files into Excel
from Word several times a day.

With ActiveSheet.QueryTables.Add(Connection:="Text Files (*.txt), *.txt", _
Destination:=Range("A2"))
.Name = "subcount"
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Try it like this. I've never used the QueryTables before so that might
be where the wrong syntax is. Let me know what line you get the error
on, if you still get the error.

Charles

Dim File1 As String

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Text Files", "*.txt"
.Title = "Please Select Text File to Import"
If .Show Then
File1 = .SelectedItems(1)
MsgBox File1
Else
MsgBox "User Clicked Cancel"
End If
End With

With ActiveSheet.QueryTables.Add(Connection:=File1 _
Destination:=Range("A2"))
.Name = "subcount"
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

I get the error on this code:
"With ActiveSheet.QueryTables.Add(Connection:=File1 _
Destination:=Range("A2"))"

"Die_Another_Day" wrote:

Try it like this. I've never used the QueryTables before so that might
be where the wrong syntax is. Let me know what line you get the error
on, if you still get the error.

Charles

Dim File1 As String

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Text Files", "*.txt"
.Title = "Please Select Text File to Import"
If .Show Then
File1 = .SelectedItems(1)
MsgBox File1
Else
MsgBox "User Clicked Cancel"
End If
End With

With ActiveSheet.QueryTables.Add(Connection:=File1 _
Destination:=Range("A2"))
.Name = "subcount"
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub