Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    querytables.add (XL97 sr2b & XL2K sr1)

    The following line works in XL 2K:
    <pre>Dim fCSV As Variant
    fCSV = Application.GetOpenFilename("(*.csv), *.*", , "Select the CSV file to import:")
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fCSV, Destination:=Range("A1"))</pre>


    but it fails in XL97 because (if I've correctly compared the XL97 and XL2K help files) it appears that 97
    doesn't support <pre>Connection :="TEXT;"</pre>

    How do you do this in XL97?

    stuck

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: querytables.add (XL97 sr2b & XL2K sr1)

    Are you trying to open a text file? You can use the "OpenText" method

    Dim fCSV As Variant
    fCSV = Application.GetOpenFilename("(*.csv), *.*", , "Select the CSV file to import:")
    Workbooks.OpenText FileName:=fCSV 'add other parameters as desired

    Once you open it you can manipulate it as desired.

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: querytables.add (XL97 sr2b & XL2K sr1)

    Yes, it's a text file and yes your suggestion does seem to be what I want.

    There is one gotcha though. Although the fields in my csv file use a double quote text qualifier around some numbers and I have added the parameter <pre> TextQualifier:=xlDoubleQuote </pre>

    and also stipulated within <pre>FieldInfo:=Array(Array( ...etc)</pre>

    which columns are to be text XL ignores this and insists on formatting the numbers as numbers. HOWEVER! If I change the file extension from .csv to .txt all is well and I get what I'm after., numbers formatted as text.

    I can live with this, many thanks.

    (un)stuck

Posting Permissions

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