FileDialog to Import XLS Content

[KrisS]

I am trying to use a FileDialog(msoFileDialogFilePicker) dialog box to import either delimited text from a file, or content from a XLS sheet (all cells from the first worksheet). I have the delimited-import part working, but I am unable to get the XLS import to work.

My code is attachd. Any input is appreciated.

[blane245]

Welcome to the forum!

In the second QueryTables.Add, change Connection:="TEXT;" to Connection:="ODBC;" and see if that helps.

[KrisS]

I appreciate the ODBC idea, but still no data unfortunately.


Any other input is appreciated.

[blane245]

I recorded the following macro by selecting Data->Import External Data->Import Data..., then selecting an Excel file and then Sheet1. This shows what things you need to specify in the ODBC connection string.

    With ActiveSheet.QueryTables.Add(Connection:=Array( _
        "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Documents and Settings\lane.bob\Desktop\Excel Forum\A." _
        , _
        "xls;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Datab" _
        , _
        "ase Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global " _
        , _
        "Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;J" _
        , _
        "et OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
        ), Destination:=Range("B17"))
        .CommandType = xlCmdTable
        .CommandText = Array("Sheet1$")
        .Name = "A_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = _
        "C:\Documents and Settings\lane.bob\Desktop\Excel Forum\A.xls"
        .Refresh BackgroundQuery:=False
    End With

I suggest you record what you want to have happen and then adapt it to your needs.