I have Excel 20120 and have a macro that imports a selected CSV file into an existing tab on a worksheet. It works in Windows XP, But in Windows 7 I have to pick the file twice. This is code that I made years ago. I did notice that the Dialog box's that pop up between the 2 version. The file type is displayed different. Please find my code below.
Private Sub btnImportData_Click()
Me.Hide
Application.ScreenUpdating = False
Sheets("Info").Visible = True
Sheets("Info").Select
Range("D28:D29,H11,G41,H16,N31").Value = Null 'APW, MPW,Model Yr, Notes, Program Life,EC LEVEL
Range("Q37").Select ' Todays Formula
Selection.Copy
Range("P37").Select ' Todays Date
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A24:A25,W2:W26,W28:W52,X28:X52").Value = False 'Report Selectors, Purchased Items Direct,Material Auto?,Stl or Alm?
Range("A1").Select
Sheets("Info").Visible = False
Sheets("Import").Visible = True
Sheets("Import").Select
Application.Goto Reference:="Clear"
Selection.ClearContents
Range("A1").Select
ChDrive "P"
ChDir "P:\INITIAL\"
Application.ScreenUpdating = True
FileToOpen = Application _
.GetOpenFilename("CSV Files (*.csv),*.csv") ' This grabs the whole file path
If TypeName(FileToOpen) = "Boolean" Then
Exit Sub
Else
Sheets("Info").Visible = True
Sheets("Info").Select
Range("Info!m1").Value = FileToOpen 'This puts the file path into cell M1, then cell M2 chops it down.
Sheets("Info").Visible = False
connstring = "TEXT;" & FileToOpen
'Application.ScreenUpdating = False
Sheets("Import").Select
With Selection.QueryTable
.Connection = connstring
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If
Application.ScreenUpdating = False
btnFOMOCO
End Sub
Any suggestions to accomplish what I am doing here in an easier fashion is appreciated. This has worked for years.
Private Sub btnImportData_Click()
Me.Hide
Application.ScreenUpdating = False
Sheets("Info").Visible = True
Sheets("Info").Select
Range("D28:D29,H11,G41,H16,N31").Value = Null 'APW, MPW,Model Yr, Notes, Program Life,EC LEVEL
Range("Q37").Select ' Todays Formula
Selection.Copy
Range("P37").Select ' Todays Date
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A24:A25,W2:W26,W28:W52,X28:X52").Value = False 'Report Selectors, Purchased Items Direct,Material Auto?,Stl or Alm?
Range("A1").Select
Sheets("Info").Visible = False
Sheets("Import").Visible = True
Sheets("Import").Select
Application.Goto Reference:="Clear"
Selection.ClearContents
Range("A1").Select
ChDrive "P"
ChDir "P:\INITIAL\"
Application.ScreenUpdating = True
FileToOpen = Application _
.GetOpenFilename("CSV Files (*.csv),*.csv") ' This grabs the whole file path
If TypeName(FileToOpen) = "Boolean" Then
Exit Sub
Else
Sheets("Info").Visible = True
Sheets("Info").Select
Range("Info!m1").Value = FileToOpen 'This puts the file path into cell M1, then cell M2 chops it down.
Sheets("Info").Visible = False
connstring = "TEXT;" & FileToOpen
'Application.ScreenUpdating = False
Sheets("Import").Select
With Selection.QueryTable
.Connection = connstring
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If
Application.ScreenUpdating = False
btnFOMOCO
End Sub
Any suggestions to accomplish what I am doing here in an easier fashion is appreciated. This has worked for years.