Importing CSV file Macro

bergsh

New Member
Joined
Sep 12, 2006
Messages
13
Hi Everyone,
Here is what I am trying to do. I want to prompt the user to select a csv file from a drive. Once they select the drive I want it to import the data into sheet 1. and have it formatted the same way every time. I have some code I borrowed for opening the file. But I am not sure how to tie the file selected into the import. thanks for the help in advance.


Sub GetOpenFileName()

' Set Drive letter
ChDrive "S:\"
' Set Folder
ChDir "S:\SUNDATA\LABDATA\QC20\"
' Set File Filter
Filt = "Excel Files (*.csv), *.csv"
' Set *.* to Default
FilterIndex = 5
' Set Dialogue Box Caption
Title = "Please select a file for download"
' Get FileName
Filename = Application.GetOpenFileName(FileFilter:=Filt, _
FilterIndex:=FilterIndex, Title:=Title)
' Exit if Dialogue box cancelled
If Filename = False Then
Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
Exit Sub
End If
' Display Full Path & File Name
Response = MsgBox("You selected " & Filename, vbInformation, "Proceed")
' Open Selected Workbook
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT; & Filename", Destination:=Range("A1"))
.Name = "& Filename"
.FieldNames = True
.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 = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 9, 9, 9, 9, 9, 9, 1, 9, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

End Sub
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
not sure I understand your question. The code you have assigns the filename

Code:
filename = .....

Are you wanting to use the filename in another macro?
 

bergsh

New Member
Joined
Sep 12, 2006
Messages
13
Hey thanks for the response.
What I want to do is have it import the file selected in the macro. I believe it would be: "& Filename". Then I would like to use import that file into sheet 1 of the file called sorted.xls - which I already have opened.
 

Forum statistics

Threads
1,136,652
Messages
5,677,008
Members
419,668
Latest member
DharmaK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top