
Importing text file into Excel using VBA - from a word macro
Hi Astrid,
Thanks loads for the response. I finally got Excel to load
in the text file only to find it comes in unformatted with
the following code.
Set oExcel = CreateObject("Excel.Application")
With oExcel
With .Workbooks
.OpenText FileName:=" C:\My Documents\" &
lstFiles.Value
If I attempt to execute the Excel macro that I recorded I
get an error on the word "Range" as
in "Destination:=Range". How can I get this to work? TIA.
Regards,
Paige
The complete Excel macro follows:
' .Workbooks.Add
' With .ActiveSheet.QueryTables.Add
(Connection:= _
' "TEXT;C:\My Documents\" & lstFiles.Value,
Destination:=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 With
Quote:
>-----Original Message-----
>Hi Paige,
>Next time please don't send attachments to these
newsgroups. A lot of people
Quote:
>are afraid to open them due to the risk of viruses and
there are also a lot
Quote:
>of posters who have set their newsreader not to download
messages with
>attachments. Because of this you might not get an answer.
I've posted your
>code at the end of this message, so others can review it
as well.
>I'm not really sure cause I haven't tested this, but it
may be that your
>problem is with these lines:
> With oExcel
> .Application.Visible = True
> .Workbooks.Add
> .Worksheets.Add
> .Range("A1") = "Open Text Method"
> .Range("A3").Select
> .OpenText FileName:=strFFile '\import text to
temporary worksheet
>oExcel contains an objectvariable for the application
object but the
>opentext method applies to the workbook object. Have you
tried something
>like:
>Dim oExcel as Excel.Application
>Dim oxlBook as Excel.Workbook
>Dim bStarted as Boolean
> On Error Resume Next
> 'Grab Excel if it is running
> Set oExcel = GetObject(, "Excel.Application")
> If Err <> 0 Then
> 'Excel wasn't running yet, start it now
> Set oExcel = CreateObject("Excel.Application")
> bStarted = True
> End If
> Set oxlBook = oExcel.Workbooks.Add
> With oxlBook
> .Range("A1") = "Open Text Method"
> .Range("A3").Select
> .OpenText FileName:=strFFile '\import text to
temporary worksheet
> End With
> 'Other code here
> 'Close Excel workbook
> oXLBook.Close savechanges:=xlDoNotSaveChanges
> 'Close Excel if we started it from code
> If bStarted Then
> oExcel .Quit
> End If
> 'Clean up
> Set oXLBook = Nothing
> Set oExcel = Nothing
>Orginal text from document here:
>----------------------------------------------------------
------------------
>----
>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
>' Dim oWord As Object, oRange As Object, TextToFind As
String
>' Set oWord = CreateObject("Word.Application")
> Documents.Open ("c:\myDocu~1\MyLabels.doc")
>' oWord.Documents.Open("c:\myDocu~1
\MyLabels.doc").Activate
Quote:
> 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 Text Method"
> .Range("A3").Select
> .OpenText FileName:=strFFile '\import text to
temporary worksheet
>I Get an error on the above line
>----------------------------------------------------------
------------------
>----
>Hope this helps,
>regards,
>Astrid
>So that all can benefit from the discussion, please post
all follow-ups to
>the newsgroup.
>Visit the MVP Word FAQ site at http://www.mvps.org/word/
>> Hello All,
>> I can record a macro in Excel importing a text file but
>> when I attempt to run that code from a Word macro it
>> doesn't work. Granted I'm then trying to bring in an
>> object that contains the text file. How do I do that?
TIA.
>> Regards,
>> Paige
>.