none
workbooks.opentext expected function or variable RRS feed

  • Question

  • Hi all, 

    im new to VBA, sorry in advance if my question is obvious. but i cant figure out what is causing the compile error

    i have multiple SPACE delimited files in a folder that i wish to open using  workbooks.opentext. i used the macro recorder to find out how to open one space delimited file. i then tried to open multiple selected files using the recorded VBA code. see  the relevant code at the bottom. NOTE if i use the 'Set wkbSource = Workbooks.Open(fd.SelectedItems(i)) but this wont let me open a space delimited file correctly. the error i get if i try to use workbooks.opentext is : compile error: expected function or variable with the .opentext highlighted

    

    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    'use the standard title and filters, but change the
    'initial folder
    fd.InitialFileName = "c:\wise owl\"
    fd.InitialView = msoFileDialogViewList
    'allow multiple file selection
    fd.AllowMultiSelect = True

    FileChosen = fd.Show
    If FileChosen = -1 Then

    'open each of the files chosen
    For i = 1 To fd.SelectedItems.Count


    'Set wkbSource = Workbooks.Open(fd.SelectedItems(i))
    Set wkbSource = Workbooks.OpenText(fd.SelectedItems(i) _
            , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
            xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, _
            Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
            Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
            Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1)), _
            TrailingMinusNumbers:=True)

        'save the workbooks and close
        wkbSource.Save
        wkbSource.Close True
        wkbDest.Save
        wkbDest.Close True
        
    Next i
    End If


    Monday, December 3, 2012 5:27 PM

Answers

  • The opentext method does not return an object, so change

    Set wkbSource = Workbooks.OpenText(fd.SelectedItems(i) ...... )

    to this:

    Workbooks.OpenText fd.SelectedItems(i) ......

    Set wkbSource = ActiveWorkbook

    If you press F2 int he VBE and find OpenText, this is what you will see:

    Sub OpenText(Filename As String, [Origin], [StartRow], [DataType], [TextQualifier As XlTextQualifier = xlTextQualifierDoubleQuote], [ConsecutiveDelimiter], [Tab], [Semicolon], [Comma], [Space], [Other], [OtherChar], [FieldInfo], [TextVisualLayout], [DecimalSeparator], [ThousandsSeparator], [TrailingMinusNumbers], [Local])
        Member of Excel.Workbooks

    Compare it to the Open method:

    Function Open(Filename As String, [UpdateLinks], [ReadOnly], [Format], [Password], [WriteResPassword], [IgnoreReadOnlyRecommended], [Origin], [Delimiter], [Editable], [Notify], [Converter], [AddToMru], [Local], [CorruptLoad]) As Workbook
        Member of Excel.Workbooks

    See the "As Workbook" difference? That is why your code fails - open returns a Workbook object, opentext does not.

    Monday, December 3, 2012 8:08 PM

All replies

  • The opentext method does not return an object, so change

    Set wkbSource = Workbooks.OpenText(fd.SelectedItems(i) ...... )

    to this:

    Workbooks.OpenText fd.SelectedItems(i) ......

    Set wkbSource = ActiveWorkbook

    If you press F2 int he VBE and find OpenText, this is what you will see:

    Sub OpenText(Filename As String, [Origin], [StartRow], [DataType], [TextQualifier As XlTextQualifier = xlTextQualifierDoubleQuote], [ConsecutiveDelimiter], [Tab], [Semicolon], [Comma], [Space], [Other], [OtherChar], [FieldInfo], [TextVisualLayout], [DecimalSeparator], [ThousandsSeparator], [TrailingMinusNumbers], [Local])
        Member of Excel.Workbooks

    Compare it to the Open method:

    Function Open(Filename As String, [UpdateLinks], [ReadOnly], [Format], [Password], [WriteResPassword], [IgnoreReadOnlyRecommended], [Origin], [Delimiter], [Editable], [Notify], [Converter], [AddToMru], [Local], [CorruptLoad]) As Workbook
        Member of Excel.Workbooks

    See the "As Workbook" difference? That is why your code fails - open returns a Workbook object, opentext does not.

    Monday, December 3, 2012 8:08 PM
  • Thanks Bernie, this worked perfectly and i understand why. i guess i need to be more careful when looking into the methods like you said i missed the as workbook. thanks again for the help. 

    Regards

    Tuesday, December 4, 2012 1:15 PM