How to import a CSV into an existing worksheet? (Macros)

[sjaakie]

Hello,

I am working with the code below I found on the internet. The purpose is to automatically import a CSV file with some parameters, that's working like a charm!

Sub ImportTextFile()'Import a text file using Excel's own import function.
Dim vFileName


On Error GoTo ErrorHandle


vFileName = Application.GetOpenFilename("CSV bestanden (*.csv),*.csv")


If vFileName = False Or Right(vFileName, 3) <> "csv" Then
   GoTo BeforeExit
End If


Application.ScreenUpdating = False


Workbooks.OpenText Filename:=vFileName, _
    Origin:=437, StartRow:=1, DataType:=xlDelimited, _
    TextQualifier:=xlTextQualifierNone, _
    ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, _
    Other:=False, TrailingMinusNumbers:=True, _
    Local:=True


Columns("A:A").EntireColumn.AutoFit


BeforeExit:
Application.ScreenUpdating = True
Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub

Right now I have the wish to import the intended CSV file into an existing worksheet what already contains a predefined tab. I just could make such a worksheet but I don't know how to get the macro above get the job done. It should import the CSV in tab1 while tab2 already exists.

Could someone helps me out?
Thanks in advance!

 

[Joe4]

You can import a CSV into an existing sheet.
You can get the code for this by turning on the Macro Recorder, and performing the following steps manually:

1. Turn on the Macro Recorder
2. Go into the Workbook you wish to import the CSV to
3. Go to the sheet you wish to import the CSV to
4. Go to the Data menu
5. On the "Get External Data" ribbon, select "From Text"
6. Browse and select your file
7. Go through the "Text Import Wizard", setting the proper values
8. Click ""Finish" when complete
9. Stop the Macro Recorder and view your code

 

[sjaakie]

This is the code I got:

Sub Macro1()
'
' Macro1 Macro
'


'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;[COLOR=#ff0000]LOCATION[/COLOR]" _
        , Destination:=Range("$A$1"))
        .Name = "[COLOR=#ff0000]NAME[/COLOR]"
        .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 = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Sheets("BronCSV").Select
    ActiveWindow.SmallScroll Down:=-30
End Sub

Is there a possibility to change the code so it enabled users to add files they can search by themselves with a Open File prompt?

 

[Joe4]

Have a look here which shows you how to do that: https://www.mrexcel.com/forum/excel-questions/74855-how-open-browse-dialog-vba.html