Import CSV macro Not working Properly in Windows 7

ElvisHess

Board Regular
Joined
May 4, 2006
Messages
150
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.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Watch MrExcel Video

Forum statistics

Threads
1,123,324
Messages
5,600,956
Members
414,417
Latest member
Nobu

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