Import CSV to New Excel Worksheet (QueryTables) 2

[DougTucker]

I need help on a "QueryTables" module that prompts the user for a .csv file name, then imports it into the current worksheet. The file will be ";" (semicolon) delimited. I've created the basic code using the RecordMacro feature, but I can't get it to work with the prompt for a file name. Please review the following and let me know where it's going wrong (I get an error "Application Defined or Object Defined Error" on the first line of the "With ActiveSheet" statement...

Thanks in advance for your help.

~ Doug T.

Sub TEST_ImportCSV()
'

' TEST_ImportCSV Macro
' Macro recorded 8/20/2009 
'
Dim ImportedFile As String
'
ImportedFile = Application.GetOpenFilename _
("CSV (Comma delimited) (*.csv), *.csv", , _
    "Open CSV File Exported from XYZ Application")
If ImportedFile = "" Then Exit Sub

'Insert new worksheet and name "ImportedFile"
With ActiveWorkbook.Sheets
    .Add after:=Worksheets(Worksheets.Count)
    .Name = "ImportedFile"

End With

'<<HERE'S WHERE THE ERROR OCCURS>>
    With ActiveSheet.QueryTables.Add(Connection:=ImportedFile _
        , Destination:=Range("A1"))
        .Name = "NewWorksheet"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierNone
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = ";"
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Exit Sub
End Sub

 

[SkipVought]

Hi,

Did you search your code for the word prompt?

Just change the assigned value.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[HughLerwill]

Perhaps;

.QueryTables.Add(Connection:="TEXT;" & ImportedFile, Destination:=.Range("A1"))

 

[SkipVought]

If the prompt property is TRUE, you 'll get a window to nagvigate to a new file or not. So you would not actually need the GetOpenFileName method.

But if you want to use the GetOpenFileName method, then set the prompt property FALSE and substitute the returned file name as Hugh illustrated above.

You don't need BOTH.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[HughLerwill]

If you just want to get the data into the sheet and do not require a persistent/ updateable connection you may consider;

....
.Refresh BackgroundQuery:=False
.Delete 'sever the connection; data remains in the sheet
End With
....

at the bottom of your With block.

 

[SkipVought]

and if you don't what to keep adding querytables each time you run...

    With ActiveSheet.QueryTables(1)
        .Connection:= "TEXT;" & ImportedFile 
        .TextFilePromptOnRefresh = False
        .Refresh BackgroundQuery:=False
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DougTucker]

Thank you, Skip and Hugh! Both of your ideas work. What I found so far is that if I set "TextFilePromptOnRefresh = True", it accomplishes the necessary prompt in a generic "File Open" window. If I set it to False and feed my own prompt code I can customize it with a header label, and prompt for .csv type (this looks a bit nicer for the user):

  ImportedFile = Application.GetOpenFilename _
    ("CSV (Comma delimited) (*.csv), *.csv", , _
        "Open File Exported from XYZ Application")

Skip, I'm not sure what you mean by "and if you don't what to keep adding querytables each time you run...". Where will the Querytables be added? My intent is to add a new worksheet to the current Excel file (if they choose to import more than once it will add extra sheets each time). Is that what you mean by "addding querytables each time"?

 

[SkipVought]

QueryTables is a Collection of the Worksheet Object.

Each time you ADD a QueryTable (your code ADDS a QT) the number of QT's in that sheet increases by ONE.

If you look in the Name Box, you will see ALL your QT's, as each has a Range Name. You probably only need ONE.

Some coders will DELETE the existing QT and then ADD a new one. I don't see the point of doing that. I just modify the appropriate property and refresh.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}