Hi all,
I'm new to Excel VBA and currently working on importing CSV file to excel. Here is my situation:
I want write a Macro. When people run this Macro, they can choose which CSV to open: the easiest way is a window pops out and let them to select; It can also be like they type the name and location of the CSV file at the beginning, then the Macro can open the CSV file according to the content in the cell. As long as the Macro can open the file according to people's choice, it is good.
I have tried 2 ways and I will explain them soon:
First, I tried one example I found online. It has a window popping out and lets me to select the file. The problem is that in the CSV file, there is some content like "5-1", Excel will automatically transfer this to date, which is not I want. Even if I set the cell format as text in advance, it doesn't work at all. The code is as follows:
Sub CSV_Import()
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("test") 'set to current worksheet name
strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")
With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
.PreserveFormatting = True
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End With
End SubSecond, I used Record Macro function in excel and got the following code:
Sub Macro2()
'
' Macro2 Macro
'
'
Sheets("CSV_file").Select
ActiveWorkbook.Queries.Add Name:="NewTest1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""C:\Users\chenro1\Desktop\capstone project\NewTest1.csv""),[Delimiter="","", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}," & _
" {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=NewTest1;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [NewTest1]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "NewTest1"
.Refresh BackgroundQuery:=True
End With
End Sub
It works fine, but it is strictly to the specific file name and location. I have no idea how to make any change to let me select what I want to import.
Anyone can give me some advice of how to achieve my goal?
I'm so sorry that this question is too long. I appreciate in advance for all people that read the question and leave their comments.
Thanks,
Robert