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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,217,386
Messages
6,136,284
Members
450,002
Latest member
bybynhoc

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
Back
Top