Importing text file into Excel using VBA - from a word macro 
Author Message
 Importing text file into Excel using VBA - from a word macro

Hi Paige,

Next time please don't send attachments to these newsgroups. A lot of people
are afraid to open them due to the risk of viruses and there are also a lot
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
    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.*-*-*.com/



Quote:
> 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



Fri, 02 Jan 2004 22:37:27 GMT  
 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

- Show quoted text -

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

>.



Wed, 07 Jan 2004 05:11:39 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. importing text from a word file into VBA

2. Problems using VBA to link or DAO to import Excel file into Access97

3. Excel Replace/Import equivelant in Word VBA - Urgent

4. Excel Replace/Import equivelant in Word VBA - Urgent

5. Loading text files into Excel using VBA

6. Import excel spreadsheet to claendar using a macro

7. VBA macro to import pst file

8. >10000 lines Excel - MailMerge with word via VBA Macro

9. Word/Excel Mailmerge via VBA macro problem

10. Using a VBA Macro to copy a VBA Macro

11. Excel Macro via Word VBA

12. Running a Word VBA sub/macro from Excel

 

 
Powered by phpBB® Forum Software