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