Hello All,

I'm trying to load a text file into a worksheet - from a Word/VBA macro. I've tried several forms of that syntax but can't seem to get it right. I would appreciate some help. TIA.

Regards,

Paige

The code below works until I attempt to execute:

.OpenText FileName:=strFFile '\import text to temporary worksheet

which is approx. line 54 from here. Of course I'm not sure anything after that will work either.

Option Explicit
Option Base 1
'
Dim strFFile As String

Private Sub UserForm_Initialize()
'
Dim strFileArray() As String
Dim intCount As Integer

'
strFFile = Dir("C:\My Documents\*.txt")
intCount = 1
'
Do While strFFile <> ""
If strFFile <> "." And strFFile <> ".." Then
ReDim Preserve strFileArray(intCount)
strFileArray(intCount) = strFFile
intCount = intCount + 1
strFFile =Dir()
End If
'
Loop
'
lstFiles.List() = strFileArray
'
End Sub
'

Private Sub CommandButton2_Click()
'**
'IF ! FILE('Labelfil.dbf')
' =messagebox("No Input File Selected. Please select a file or Exit", 0, "Input File Error")
'Else
' oWord = CreateObject("Word.Application")
Documents.Open ("c:\myDocu~1\MyLabels.doc")
If Documents.Count = 0 Then _
MsgBox "This procedure will not run without a " _
& "document open.", vbOKOnly + vbExclamation, _
"No Document Is Open"


Dim oExcel As Object
Set oExcel = CreateObject("Excel.Application")
With oExcel
.Application.Visible = True
.Workbooks.Add
.Worksheets.Add
.Range("A1") = "Open TextMethod"
.Range("A2").Select

.OpenText FileName:=strFFile '\import text to temporary worksheet
.ActiveSheet.UsedRange.Select '\ select imported text in temporary workbook
.Selection.Copy '\ copy to clipboard
.Close '\ close temporary workbook
.ActiveSheet.Paste '\ paste text into your workbook

.QueryTables.Add(Connection:= _
"TEXT;C:\My Documents\Labels.txt", Destination:=oExcel.Range("A1"))
.Name = "Labels"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
'End Sub