Hi guys,
Hopefully you can help me out, i want to import an CSV file into an table.
Normally i setup a connection and i use that connection to import the csv file.
But more people are using this excel file and a person is using office 365 and the connection is not working in the new excel version.
So now i want to use vba code to import an csv file to a table.
This is the code i found on the internet.
Importing an csv is working, but how do i get it in a table?
And each time i import the csv file, i want that the old data is removed and only the new data is imported.
Hopefully you can help me out.
Maybe there is a much better vba code to fix this
Sub IZMDATA_COMPLEET_Importeren_vba()
Dim txtFileNameAndPath As String
Dim ImportingFileName As String
Dim SheetName As Worksheet
Dim fd As Office.FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
'Enable this option if you want the use to be able to select multiple files
.AllowMultiSelect = False
'This sets the title of the dialog box.
.Title = "Please select the file."
'Sets the associated filters for types of files
.Filters.Clear
.Filters.Add "csv", "*.csv"
.Filters.Add "All Files", "*.*"
' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
If .Show = True Then
txtFileNameAndPath = .SelectedItems(1)
Else
MsgBox "Please start over. You must select a file to import"
'You don't want the sub continuing if there wasn't a file selected
Exit Sub
End If
End With
'Extracts only the file name for reference later
ImportingFileName = Right(txtFileNameAndPath, _
Len(txtFileNameAndPath) - InStrRev(txtFileNameAndPath, ""))
Worksheets("IZMdata_compleet").Activate
Range("A7:KR1000").Select
Range("A7:KR1000").ClearContents
Selection.ClearContents
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
Worksheets("IZMdata_compleet").Activate
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & txtFileNameAndPath _
, Destination:=Worksheets("IZMdata_compleet").Range("$A$7"))
.Name = "Data Provision"
.FieldNames = False
.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 = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
'Fill in the array in TextFileColumnDataTypes according to
'the type of data types that you are looking to import
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
Already thanks.
Wilco,
Hopefully you can help me out, i want to import an CSV file into an table.
Normally i setup a connection and i use that connection to import the csv file.
But more people are using this excel file and a person is using office 365 and the connection is not working in the new excel version.
So now i want to use vba code to import an csv file to a table.
This is the code i found on the internet.
Importing an csv is working, but how do i get it in a table?
And each time i import the csv file, i want that the old data is removed and only the new data is imported.
Hopefully you can help me out.
Maybe there is a much better vba code to fix this
Sub IZMDATA_COMPLEET_Importeren_vba()
Dim txtFileNameAndPath As String
Dim ImportingFileName As String
Dim SheetName As Worksheet
Dim fd As Office.FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
'Enable this option if you want the use to be able to select multiple files
.AllowMultiSelect = False
'This sets the title of the dialog box.
.Title = "Please select the file."
'Sets the associated filters for types of files
.Filters.Clear
.Filters.Add "csv", "*.csv"
.Filters.Add "All Files", "*.*"
' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
If .Show = True Then
txtFileNameAndPath = .SelectedItems(1)
Else
MsgBox "Please start over. You must select a file to import"
'You don't want the sub continuing if there wasn't a file selected
Exit Sub
End If
End With
'Extracts only the file name for reference later
ImportingFileName = Right(txtFileNameAndPath, _
Len(txtFileNameAndPath) - InStrRev(txtFileNameAndPath, ""))
Worksheets("IZMdata_compleet").Activate
Range("A7:KR1000").Select
Range("A7:KR1000").ClearContents
Selection.ClearContents
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
Worksheets("IZMdata_compleet").Activate
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & txtFileNameAndPath _
, Destination:=Worksheets("IZMdata_compleet").Range("$A$7"))
.Name = "Data Provision"
.FieldNames = False
.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 = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
'Fill in the array in TextFileColumnDataTypes according to
'the type of data types that you are looking to import
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
Already thanks.
Wilco,
