Need Macro Help to Look at Desktop for all *.CSV files

April 6, 2012 at 19:34:02
Specs: Windows XP
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..


See More: Need Macro Help to Look at Desktop for all *.CSV files

Report •

#1
April 6, 2012 at 21:16:06
First, a posting tip:

Before you post any more VBA code in this forum, please click on the <font color = blue<b>blue line at the bottom of this post and read the instructions found via that link.

Try this:

Sub CSV_Connect()
'Set path to Desktop
  ChDir "C:\Documents and Settings\my.computer\Desktop\"
'Display Open File dialog box
   fileToOpen = Application.GetOpenFilename("csv Files (*.csv), *.csv")
'Quit if Cancel selected
  If fileToOpen = False Then Exit Sub
'Use fileToOpen
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & fileToOpen, Destination:=Range("A1"))
...etc.
...etc.
...etc.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 7, 2012 at 08:06:40
Thanks.. sorry about not formatting,
I get a error pop up "Compile error: Syntax error"
=================
Sub CSV_Connect()
'Set path to Desktop
  ChDir "C:\Documents and Settings\my.computer\Desktop\"
'Display Open File dialog box
   fileToOpen = Application.GetOpenFilename("csv Files (*.csv), *.csv")
'Quit if Cancel selected
  If fileToOpen = False Then Exit Sub
'Use fileToOpen
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & fileToOpen, 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

===============


Report •

#3
April 7, 2012 at 10:53:54
I just copied the code you posted in your latest response and pasted it into a new workbook, changed my.computer to my actual username and it ran with no errors.

If you are getting a syntax error, then some line (or lines) must be showing as red in the VBA editor.

I don't get any errors - using the exact code that you posted except for that one change - so I can't offer any advice other than to look for the line (or lines) that appear in red.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 8, 2012 at 06:03:40
Thanks... Got it working

Report •

Ask Question