Help with VBA Macro needed

[Me]

I created this Macro, but want to make a change but dont know how..
Hopefully someone can offer assitance.

Question1.
Instead of it looking for the file 1.csv, I want to look for all .CSV files on my desktop,
and allow me to choose the one to import


---------------------------------------------------------------------------------------------------
Sub Macro3()
'
' Macro3 Macro
' Macro recorded 4/5/2012

' Keyboard Shortcut: Ctrl+d
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\my.computer\Desktop\1.csv", Destination _
:=Range("A1"))
.Name = "1"
.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, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 2, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
------------------------------------------------------------------------------------------


Thankss..


--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-

 

[Auric__]

I created this Macro, but want to make a change but dont know how..
Hopefully someone can offer assitance.

Question1.
Instead of it looking for the file 1.csv, I want to look for all .CSV
files on my desktop, and allow me to choose the one to import

Try this:

Sub allCSVsOnDesktop()
nextFile = Dir$("C:\users\Erik\Desktop\*.csv")
While Len(nextFile)
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\my.computer\Desktop\" & nextFile, _
Destination:=Range("A1"))
'everything you want to do with the table goes here
End With
nextFile = Dir$
Wend
End Sub

 

[Auric__]

nextFile = Dir$("C:\users\Erik\Desktop\*.csv")

Whoops... that should be:

nextFile = Dir$("C:\Documents and Settings\my.computer\Desktop\*.csv")

Forgot to change it from my "test" settings.

 

[rumkus]

As he walked into the blinding golden light he turned, and looked

back over his shoulder. He smiled at me... I never saw him again.

Keep stalking Auric.
I am sure you will see them again in the police station