Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passing ParamArray argument for OpenText (VBA / Excel 97)

    I've got a small function that creates a spreadsheet based on a text file. I've changed the function recently to take an extra ParamArray argument that I want to pass as 'FieldInfo. Code as follows...

    Public Sub CreateSpreadsheet(strFileName As String, strDelimeter As String, ParamArray FieldInfoArray() As Variant)

    'Handle Errors
    On Error GoTo SheetError

    'Load the file via Excel
    If IsEmpty(FieldInfoArray) Then
    msExcel.Workbooks.OpenText FileName:=strFileName, Origin:=xlWindows, _
    StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
    , Space:=False, Other:=True, OtherChar:=strDelimeter
    Else
    msExcel.Workbooks.OpenText FileName:=strFileName, Origin:=xlWindows, _
    StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
    , Space:=False, Other:=True, OtherChar:=strDelimeter, FieldInfo:= FieldInfoArray
    End If

    SheetError_Resume:
    Exit Sub

    SheetError:
    Resume SheetError_Resume

    End Sub

    I would then call the routine as follows...

    Call CreateSpreadsheet("MyFileName", "My Delimiter", Array(1, 1), Array(2, 1), Array(3, 4))

    I'm obviously doing something wrong at the moment as I'm just getting a Type Mismatch error. What do I need to do with the FieldInfo:= FieldInfoArray syntax in order for it to take the arguments...??

    Many Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing ParamArray argument for OpenText (VBA / Excel 97)

    Hi Dylan,

    The problem is not in the assignment of FieldInfoArray but in IsEmpty. This should be IsMissing:

    If IsMissing(FieldInfoArray) Then

    If you don't specify FieldInfoArray, IsEmpty returns False, so the code jumps to the Else part, but then FieldInfo:=FieldInfoArray fails.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Passing ParamArray argument for OpenText (VBA / Excel 97)

    Dylan,
    I've never actually used this but does:
    Call CreateSpreadsheet("MyFileName", "My Delimiter", Array(Array(1, 1), Array(2, 1), Array(3, 4)))
    work for you?

    [Edit: Ignore me - the conversion should be implicit as you've used a paramarray! Hans has, as usual, spotted the problem]
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing ParamArray argument for OpenText (VBA / Excel 97)

    Thanks for the responses....I forget about the IsEmpty bit but I just looked at the documentation on ParamArrays and you have to test the UBound & LBound limits...! (If IsMissing is used on a ParamArray argument, it always returns False. To detect an empty ParamArray, test to see if the array

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing ParamArray argument for OpenText (VBA / Excel 97)

    As I wrote, I tested this from within Excel (97 SR-2). There, IsMissing returns True if I pass at least one argument after the delimiter, and False otherwise.

    In what application are you testing this? Another Office app, or VB6, or ...?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing ParamArray argument for OpenText (VBA / Excel 97)

    In addition to my previous reply: in the meantime, I also tested with VB6, using Automation to manipulate Excel. Same result: IsMissing returns False if FieldInfoArray is specified, True otherwise, so the code runs as intended. I don't understand why you have problems <img src=/S/confused.gif border=0 alt=confused width=15 height=20>.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Passing ParamArray argument for OpenText (VBA / Excel 97)

    Dylan,
    Yes you're passing an array of arrays. For your example, something like this:
    <pre>Function testarrparam(ParamArray arrParams())
    Dim i As Integer, j As Integer
    On Error Resume Next
    For i = 0 To UBound(arrParams) - 1
    For j = 0 To 1
    Debug.Print "i = " & i & ";j = " & j & ";val = " & arrParams(i)(j)
    Next j
    Next i

    End Function
    </pre>

    should step through the values.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing ParamArray argument for OpenText (VBA / Excel 97)

    Thanks for that example Rory, should be very useful...

    Hans - It's not the IsMissing bit that's giving me problems - It's the actual FieldInfo:=FieldInfoArray() in the OpenText section. I'm skipping past the IF > ELSE section and going directly to this bit because I'm always passing ParamArray info.

    In your test, did you mange to get Excel to create a worksheet where FieldInfoArray() contained some array arguments? It just type mismatches for me...?

    Thanks

  9. #9
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing ParamArray argument for OpenText (VBA / Excel 97)

    Sorry, my fault this one....I didn't tell you the whole story!

    I was calling another function before calling the CreateSpreadsheet function, passing the ParamArray to the first function which in turn passed the arguments to CreateSpreadsheet.

    I didn't realise that the ParamArray was losing it's values as it got passed onto the second function, must be because it's all ByVal. Hence the Type Mismatch.

    Thanks for all the help

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing ParamArray argument for OpenText (VBA / Excel 97)

    I don't understand at all. I have copied the code from your original post and pasted it into a VB6 module. Thew only thing I added is

    Dim msExcel As Excel.Application

    (and of course, I set a reference to the Excel object library)

    When I call your code from a command button on a form, it opens the text file correctly (I checked this by single stepping through the code)...

  11. #11
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing ParamArray argument for OpenText (VBA / Excel 97)

    Hans, apologies - see my last post...

    I was passing the ParamArray through another function first, this was causing it to lose it's dimensions as it was sent to CreateSpreadsheet. I didn't know because I couldn't test the Array info until Rory's example...!

    Thanks for any time you spent on this anyway, sorry for any confusion...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •