Importing CSV into existing non-active sheet.

[Character7]

I have fooled around with several different import VBA scripts I have seen littering the internet. I patch a couple together to get my desired result and am now pulling out my hair. For the love of peanut butter, please tell me what the heck is wrong with this portion of my import code?

With IncomingData.QueryTables.Add(Connection:= _
"TEXT;" & fStr, Destination:=IncomingData.Range("$A$1"))

*** Note, IncomingData is the name of the inactive sheet, the button which is activating this script is on a sheet called Dwelling.
*** Note, fStr as you may know is a reference to a function that opens a dialogue to choose said CSV from file system.

I just need to know how to clean this up so that when button is pressed on sheet "Dwelling", that it copies the CSV into sheet "IncomingData" starting at grid A1.

 

[pvr928]

Hi Character7

Try:

With IncomingData.QueryTables.Add(Connection:= "TEXT;" & fStr, Destination:=[B]Sheets("[/B]IncomingData[B]")[/B].Range("$A$1"))

Cheers

pvr928

 

[Character7]

I was able to get the result I was looking for using a combination of the information that you provided and a few other sources. My follow-up question is prior to importing the CSV, or during import, is it possible to modify this script to strip carriage returns before the information is loaded into the target worksheet? I have provided the code below which is attached to a clickable button within the workbook.

-----------------------------

Private Sub Import_Click()
Worksheets("IncomingData").Cells.Clear
With Worksheets("IncomingData").QueryTables.Add(Connection:= _
"TEXT;" & GetFile, Destination:=Worksheets("IncomingData").Range("$A$1"))
.Name = "logexportdata"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 2
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(xlGeneralFormat)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Function GetFile() As String
Dim filename__path As Variant
filename__path = Application.GetOpenFilename(FileFilter:="Csv (*.CSV), *.CSV", Title:="Select File To Be Opened")
If filename__path = False Then Exit Function
GetFile = filename__path
End Function