Macro for CSV Import

[Aqusmacro]

Hi I have a macro I found online but would like to make modifications to it.
1st would like to know if I can modify so that it brings me directly to directory I want where my csv files are. ex my files are in E:\financialexcel\transactions so when I click on macro I want it to take me directly to that folder then I can choose file I want to import.
2. I need to import different files therefore would like to import 2nd file right after data from first file.
Also noticed if I run the macro more than once I get a runtime error 9, subscript out of range and the following line is highlighted in yellow
ActiveWorkbook.Sheets(2).QueryTables("importCSVimporter").Delete
here is the macro I am using. thanks

Sub importCSV()

Dim column_types() As Variant
csv_path = Application.GetOpenFilename()
If csv_path = False Then
Exit Sub
End If
For i = 0 To 16384
ReDim Preserve column_types(i)
column_types(i) = 2
Next i
With ActiveWorkbook.Sheets(2).QueryTables.Add(Connection:="TEXT;" & csv_path, Destination:=Range("A1"))
.Name = "importCSVimporter"
.FieldNames = True
.AdjustColumnWidth = True
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = column_types
.Refresh BackgroundQuery:=False
End With

ActiveWorkbook.Sheets(2).QueryTables("importCSVimporter").Delete

End Sub

 

[Legacy 143009]

I have no chance to test but some of these may solve your problems:

Sub importCSV()

  Dim column_types() As Variant
  ChDrive "E:\"
  ChDir "E:\financialexcel\transactions\"
  csv_path = Application.GetOpenFilename()
  If csv_path = False Then
    Exit Sub
  End If
  For i = 0 To 16384
    ReDim Preserve column_types(i)
    column_types(i) = 2
  Next i
  With ActiveWorkbook.Sheets(2).QueryTables.Add(Connection:="TEXT;" & csv_path, Destination:=Cells(Rows.Count, "A").End(xlUp).Offset(1))
  .Name = "importCSVimporter"
  .FieldNames = True
  .AdjustColumnWidth = True
  .TextFileStartRow = 1
  .TextFileParseType = xlDelimited
  .TextFileTextQualifier = xlTextQualifierDoubleQuote
  .TextFileConsecutiveDelimiter = False
  .TextFileTabDelimiter = False
  .TextFileSemicolonDelimiter = True
  .TextFileCommaDelimiter = True
  .TextFileSpaceDelimiter = False
  .TextFileColumnDataTypes = column_types
  .Refresh BackgroundQuery:=False
  End With

  ActiveWorkbook.Sheets(2).QueryTables(1).Delete
  'If this line doesn't work, try (0) instead of (1)

End Sub

 

[Aqusmacro]

I have no chance to test but some of these may solve your problems:

Sub importCSV()

  Dim column_types() As Variant
  ChDrive "E:\"
  ChDir "E:\financialexcel\transactions\"
  csv_path = Application.GetOpenFilename()
  If csv_path = False Then
    Exit Sub
  End If
  For i = 0 To 16384
    ReDim Preserve column_types(i)
    column_types(i) = 2
  Next i
  With ActiveWorkbook.Sheets(2).QueryTables.Add(Connection:="TEXT;" & csv_path, Destination:=Cells(Rows.Count, "A").End(xlUp).Offset(1))
  .Name = "importCSVimporter"
  .FieldNames = True
  .AdjustColumnWidth = True
  .TextFileStartRow = 1
  .TextFileParseType = xlDelimited
  .TextFileTextQualifier = xlTextQualifierDoubleQuote
  .TextFileConsecutiveDelimiter = False
  .TextFileTabDelimiter = False
  .TextFileSemicolonDelimiter = True
  .TextFileCommaDelimiter = True
  .TextFileSpaceDelimiter = False
  .TextFileColumnDataTypes = column_types
  .Refresh BackgroundQuery:=False
  End With

  ActiveWorkbook.Sheets(2).QueryTables(1).Delete
  'If this line doesn't work, try (0) instead of (1)

End Sub

works perfectly a huge thank-you

 

[Legacy 143009]

Glad it did work It was totally a blind shot )

 

[Aqusmacro]

I have no chance to test but some of these may solve your problems:

Sub importCSV()

  Dim column_types() As Variant
  ChDrive "E:\"
  ChDir "E:\financialexcel\transactions\"
  csv_path = Application.GetOpenFilename()
  If csv_path = False Then
    Exit Sub
  End If
  For i = 0 To 16384
    ReDim Preserve column_types(i)
    column_types(i) = 2
  Next i
  With ActiveWorkbook.Sheets(2).QueryTables.Add(Connection:="TEXT;" & csv_path, Destination:=Cells(Rows.Count, "A").End(xlUp).Offset(1))
  .Name = "importCSVimporter"
  .FieldNames = True
  .AdjustColumnWidth = True
  .TextFileStartRow = 1
  .TextFileParseType = xlDelimited
  .TextFileTextQualifier = xlTextQualifierDoubleQuote
  .TextFileConsecutiveDelimiter = False
  .TextFileTabDelimiter = False
  .TextFileSemicolonDelimiter = True
  .TextFileCommaDelimiter = True
  .TextFileSpaceDelimiter = False
  .TextFileColumnDataTypes = column_types
  .Refresh BackgroundQuery:=False
  End With

  ActiveWorkbook.Sheets(2).QueryTables(1).Delete
  'If this line doesn't work, try (0) instead of (1)

End Sub

quick question is there a way I can change
ChDrive "E:\"
ChDir "E:\financialexcel\transactions\"

to just point to same folder or a subfolder of the workbook file I use? this way if I move my files around I don't have to correct all my macros?

Also how can I run this macro from another sheet? I have modified this macro to copy info extracted in sheet 2 to another sheet called transactions. But If I try to run the macro from transactions I get an error. I can only run it if sheet 2 is the active sheet
thanks

 

[Legacy 143009]

For the first part of your question, try:

ChDir ActiveWorkbook.Path

For the second part, you are right. Replace all Sheet(2) with ActiveSheet. Create a standard module and paste the code in it. You may place a button in each sheet and assing that macro. Or you can run directly from inside module.

 

[Aqusmacro]

For the first part of your question, try:

ChDir ActiveWorkbook.Path

For the second part, you are right. Replace all Sheet(2) with ActiveSheet. Create a standard module and paste the code in it. You may place a button in each sheet and assing that macro. Or you can run directly from insid

For the first part of your question, try:

ChDir ActiveWorkbook.Path

For the second part, you are right. Replace all Sheet(2) with ActiveSheet. Create a standard module and paste the code in it. You may place a button in each sheet and assing that macro. Or you can run directly from inside module.

hi thanks.
for the first part it worked but I had to insert also ChDrive ActiveWorkbook.

for the second part your idea is good but can only run the macro if I am in that sheet. What I want to do is run the macro from a button in my transaction tab but info is imported in sheet 2 now called import.
If I am in transaction tab and run the macro which is set to run in sheet 2 I get an error in
With ws.QueryTables.Add(Connection:="TEXT;" & csv_path, Destination:=Cells(Rows.Count, "A").End(xlUp).Offset(1))

reason I want to do this in my transaction tab I have my final results I work with and in sheet 2 I extract the info from the csv download.
here is the full macro which might clarify. at bottom I copy paste info from sheet 2 to transactions . but don't want to switch to sheet2 just to run the macro
BTW I changed the name of sheet 2 to Import
Thank-you

sub bank()

'macro to copy from column a to g use for bank

  Dim column_types() As Variant
  'ChDrive "E:\"
  'ChDir "E:\financialexcel\transactions\"
  

    ChDrive ActiveWorkbook.Path
    ChDir ActiveWorkbook.Path
 

  
  ' if I want to link file name copy directly in brackets
  csv_path = Application.GetOpenFilename()
  If csv_path = False Then
    Exit Sub
  End If
  
  Set ws = Sheets("import")
  For i = 0 To 16384
    ReDim Preserve column_types(i)
    column_types(i) = 2
  Next i
  With ws.QueryTables.Add(Connection:="TEXT;" & csv_path, Destination:=Cells(Rows.Count, "A").End(xlUp).Offset(1))
  .Name = "importCSVimporter"
  .FieldNames = True
  .AdjustColumnWidth = True
  .TextFileStartRow = 1
  .TextFileParseType = xlDelimited
  .TextFileTextQualifier = xlTextQualifierDoubleQuote
  .TextFileConsecutiveDelimiter = False
  .TextFileTabDelimiter = False
  .TextFileSemicolonDelimiter = True
  .TextFileCommaDelimiter = True
  .TextFileSpaceDelimiter = False
  .TextFileColumnDataTypes = column_types
  .Refresh BackgroundQuery:=False
  End With

  ws.QueryTables(1).Delete
  'If this line doesn't work, try (0) instead of (1)
  
  '   InsertMissingColumns()
  '   Check to see if column B is not Card Number
    If Range("B1").Value <> "card Number" Then
'       Insert blank column
        Columns("B:B").insert
        Range("B1").Value = "card Number"
    End If
    

  
'Declare variables
Dim ws1 As Worksheet
Dim ws2 As Worksheet

'Set variables to the worksheets
Set ws1 = Sheets("import")
Set ws2 = Sheets("transactions")

'Copy
ws1.Range("A2", ws1.Range("g" & Rows.Count).End(xlUp).Offset(1)).copy

'Paste
ws2.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

Application.CutCopyMode = False

   ws1.Range("A1", ws1.Range("h" & Rows.Count)).Delete

    
   
 End Sub

 

[Legacy 143009]

I didn't get it quite. Try like:

ws.QueryTables.Add(Connection:="TEXT;" & csv_path, Destination:=ws.Cells(Rows.Count, "A").End(xlUp).Offset(1))

 

[Aqusmacro]

I didn't get it quite. Try like:

ws.QueryTables.Add(Connection:="TEXT;" & csv_path, Destination:=ws.Cells(Rows.Count, "A").End(xlUp).Offset(1))

super exactly what I was looking for. Thanks again

 

[Legacy 143009]

No problem