Sub csvImportTest()
'
' csvImportTest Macro
'
'
Dim csvFile As Variant
csvFile = Application.GetOpenFilename(FileFilter:="csv files (*.csv), *.csv", MultiSelect:=False, Title:="Select CSV file to import")
If csvFile = False Then Exit Sub 'Cancel clicked
On Error Resume Next
ActiveWorkbook.Queries("Table 4").Delete
On Error GoTo 0
ActiveWorkbook.Queries.Add Name:="Table 4", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""" & csvFile & """),[Delimiter="","", Columns=60, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Sample ID"", type text}, {""Samp" & _
"le Rack ID"", type text}, {""Sample Rack Position"", Int64.Type}, {""Operator ID at time of Processing"", type text}, {""Assay Name"", type text}, {""Assay Number"", Int64.Type}, {""Assay Version"", Int64.Type}, {""Final Result Value"", type number}, {""Units"", type text}, {""Instrument Response Value (RLU)"", Int64.Type}, {""Dilution Name"", type text}, {""Result " & _
"Flags"", type text}, {""Result Code"", type text}, {""Date of Completion"", type date}, {""Time of Completion"", type time}, {""Run Number"", Int64.Type}, {""Result Comment"", type text}, {""Module Serial Number"", type text}, {""Processing Lane ID"", type text}, {""Processing Track ID"", Int64.Type}, {""Site Name"", type text}, {""System Name"", type text}, {""Syst" & _
"em Software Version"", type date}, {""Order Type"", Int64.Type}, {""Reagent Lot Number"", type text}, {""Reagent Lot Expiration Date"", type date}, {""Reagent Serial Number"", Int64.Type}, {""Trigger Lot Number"", type text}, {""Trigger Expiration Date"", type date}, {""Pre-Trigger Lot Number"", type text}, {""Pre-Trigger Expiration Date"", type date}, {""Buffer Lot" & _
" Number"", type text}, {""Buffer Expiration Date"", type date}, {""RV Lot Number"", Int64.Type}, {""Calibration Date"", type date}, {""Calibration Time"", type time}, {""Calibrator Lot Number"", type text}, {""Calibrator Serial Number"", Int64.Type}, {""Control Type"", type text}, {""Control Set Name"", type text}, {""Control Name"", type text}, {""Control Lot Numbe" & _
"r"", type text}, {""Control Lot Expiration"", type datetime}, {""Control Serial Number"", Int64.Type}, {""Assay Control Lot Number"", type text}, {""Assay Control Serial Number"", Int64.Type}, {""Control Min Range"", type number}, {""Control Max Range"", type number}, {""Control Range Units"", type text}, {""Secondary Reagent Lot Number"", type text}, {""Secondary R" & _
"eagent Lot Expiration Date"", type datetime}, {""Secondary Reagent Serial Number"", Int64.Type}, {""Calibration Cutoff Value"", type number}, {""Positive Cal Mean RLU"", Int64.Type}, {""Constituent Assay 1 Name"", type text}, {""Constituent Assay 1 Result Value"", type text}, {""Constituent Assay 1 Instrument Response"", Int64.Type}, {""Constituent Assay 2 Name"", t" & _
"ype text}, {""Constituent Assay 2 Result Value"", type text}, {""Constituent Assay 2 Instrument Response"", Int64.Type}})," & Chr(13) & "" & Chr(10) & " #""Removed Columns"" = Table.RemoveColumns(#""Changed Type"",{""Sample Rack ID"", ""Sample Rack Position"", ""Assay Number"", ""Assay Version"", ""Instrument Response Value (RLU)"", ""Dilution Name"", ""Result Flags"", ""Result Code"", ""Ru" & _
"n Number"", ""Result Comment"", ""Processing Lane ID"", ""Processing Track ID"", ""Site Name"", ""Module Serial Number"", ""System Software Version"", ""Order Type"", ""Reagent Lot Number"", ""Reagent Lot Expiration Date"", ""Reagent Serial Number"", ""Trigger Lot Number"", ""Trigger Expiration Date"", ""Pre-Trigger Lot Number"", ""Pre-Trigger Expiration Date"", ""B" & _
"uffer Lot Number"", ""Buffer Expiration Date"", ""RV Lot Number"", ""Calibration Date"", ""Calibration Time"", ""Calibrator Lot Number"", ""Calibrator Serial Number"", ""Control Serial Number"", ""Assay Control Lot Number"", ""Assay Control Serial Number"", ""Secondary Reagent Lot Number"", ""Secondary Reagent Lot Expiration Date"", ""Secondary Reagent Serial Number" & _
""", ""Calibration Cutoff Value"", ""Positive Cal Mean RLU"", ""Constituent Assay 1 Name"", ""Constituent Assay 1 Result Value"", ""Constituent Assay 1 Instrument Response"", ""Constituent Assay 2 Name"", ""Constituent Assay 2 Result Value"", ""Constituent Assay 2 Instrument Response""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Removed Columns"""
' ActiveSheet.ListObjects("Table 4").Name = _
' "Table 4"
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 4"";Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Table 4]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub