Use VBA To Import .CSV into Specific WorkSheet In Excel Workbook

[DevonDavenport]

Hey All,


I'm trying to set up an excel Macro that once a user clicks the "Import Data" button, the user is able to select from their hard drive the data they want to upload and insert into the excel.


My issue is that the Macro always imports the data into the First Worksheet within the WorkBook, I want to specify the destination of the imported data.


Here is the code I have so far that imports into the first WorkSheet


Any suggestions would be appreciated.

Sub IMPORT_DATA()
Dim fn, e
Dim x, y, n As Long, txt As String, flg As Boolean
Dim i As Long, ii As Long, a(), maxCol As Long
fn = Application.GetOpenFilename("csv,*.csv", 1, "Open CSV", MultiSelect:=True)
If Not IsArray(fn) Then Exit Sub
n = 1
For Each e In fn
If FileLen(e) > 0 Then
txt = CreateObject("Scripting.FileSystemObject") _
.OpenTextFile(e).ReadAll
x = Split(txt, vbCrLf)
ReDim a(1 To UBound(x) + 1, 1 To 20)
For i = 0 To UBound(x)
y = Split(CleanCSV(x(i), Chr(2), Chr(3)), ",")
maxCol = Application.Max(maxCol, UBound(y) + 2)
If maxCol > UBound(a, 2) Then
ReDim Preserve a(1 To UBound(a, 1), 1 To maxCol)
End If
For ii = 0 To UBound(y)
a(i + 1, ii + 1) = y(ii)
Next
Next
Sheets(1).Cells(n, 1).Resize(UBound(a, 1), maxCol).Value = a
n = n + UBound(a, 1)
End If
Next
With Sheets(1).UsedRange
.Replace Chr(2), ",", xlPart
.Replace Chr(3), vbNullString, xlPart
End With
End Sub


Function CleanCSV(ByVal txt As String, ByVal subComma As String, _
ByVal subDQ As String) As String
Dim m As Object
Static RegX As Object
If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
With RegX
.Pattern = "(^|,)(""[^""]+"")(,|$)"
Do While .test(txt)
Set m = .Execute(txt)(0)
txt = Application.Replace(txt, m.firstindex + 1, _
m.Length, m.submatches(0) & Replace(Replace(m.submatches(1), _
",", subComma), """", subDQ) & m.submatches(2))
Loop
End With
CleanCSV = txt
End Function

Continue reading...