You can get the default directory by performing a Chdrive and a chdir like
the code below. Because you are using a comma delimited file you don't need
the arrays of 1's (just need if fixed width). I also eliminated optional
options that aren't reauired and may be confusing.


Option Explicit
Sub Loader1()

Dim myFileName As Variant


CHdrive = "H"
Chdir = "H:\my documents\temp"
myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV",
_
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & myFileName, Destination:=Range("A1"))
.Name = "CreditData-021809dater"
.FieldNames = True
.RowNumbers = False
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileCommaDelimiter = True
.TextFileTrailingMinusNumbers = True
End With

Range("A1:AO1").Font.Bold = True
End Sub


"Paul H" wrote:


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

Dave,
Thank so much. It works perfectly. I just need to tell it what folder to
be looking in, to make the file selection. It will be a long path on a
networked drive.
I bought a book that should help me figure these things out myself - Excel
2007 Power Programming with VBA (Mr. Spreadsheet's Bookshelf) by John
Walkenbach.
Paul

================================================== =======
Untested:

Option Explicit
Sub Loader1()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV",
_
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & myFileName, Destination:=Range("A1"))
.Name = "CreditData-021809dater"
.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 = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Range("A1:AO1").Font.Bold = True
End Sub

Paul H wrote:

================================================== =======
Dave,
I have no idea what I'm doing - is there book on this subject. I clicked
"step into" to get into the VB editor process, then pasted your stuff in.
Should your stuff go 1st?
Paul

Sub Loader1()
'
' Loader1 Macro
' Macro recorded 6/30/2009 by Paul Hoberg
'
' Keyboard Shortcut: Ctrl+m
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\aaa\CreditData-021809dater.csv",
Destination:=Range("A1"))
.Name = "CreditData-021809dater"
.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 = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Option Explicit
Sub Testme01()

Dim myFileName As Variant
Dim Wkbk As Workbook

myFileName = Application.GetOpenFilename(filefilter:="CSV Files,
*.CSV",
_
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

set wkbk = Workbooks.Open(Filename:=Filename:=myFileName)

Range("A1").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
Range("A1:AO1").Select
Selection.Font.Bold = True
End Sub

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

"Dave Peterson" wrote in message
...
You can use as many workbooks as you want.

You can have 20 different workbooks or a single workbook. Or even a
few--where
you put the macros that generate similar reports in those few workbooks.

You could use something like this to ask for the filename:

tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant
Dim Wkbk as workbook

myFileName = Application.GetOpenFilename(filefilter:="CSV Files,
*.CSV",
_
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

set wkbk = Workbooks.Open(Filename:=Filename:=myFileName)

'....rest of recorded code here!

End Sub

Paul H wrote:

================================================== =======
I prefer to have each report stand alone. All the .CSV files for one
report
will be in one folder. Other reports will have their own folders. So
for
each of the reports, I need to have the macro let the user browse the
folder
for his preferred file. Is this possible? Each spreadsheet will only
need
one "import" button on the left end of the toolbar. Can each .XLS have
it's
own macro embedded it? Or must the user have one workbook project, that
contains the macros? This will be harder for me to maintain than if
they
stand alone.
================================================== =======

"Dave Peterson" wrote in message
...
You could make a toolbar that has the 15-20 different macros on it. But
I
think
I'd use multiple buttons from the Forms toolbar placed directly on a
worksheet.

And if some of those reports are always run at the same time as others,
then
I'd
have one button that ran those reports.

Caption one of the buttons "Click me to run reports 1-6"

And assign a "combined" macro to that button:

Option Explicit
Sub DoReports_1_6()
call DoReport1
call DoReport2
call DoReport3
call DoReport4
call DoReport5
call DoReport6
End Sub

Where those doReport# procedures are in that same workbook's project.

====
By using buttons from the Forms toolbar placed directly on the
worksheet,
you
can have as many notes as you like near that button.

Paul H wrote:

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

Dave,
One user will be using these Excel reports.
Each having many or few rows and columns.
I will be developing 15 or 20 different reports for this user.
Some will be run multiple times per day as batches are processed.
Others may only be run once a week or once a month.
The .CSV files will be in the same folder.
The user is used to pointing to the correct file for other processes.
So, the process can not require my participation.
I like putting the "import" button at the left edge of the toolbar.
Please comment...
Thanks,
Paul

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

"Dave Peterson" wrote in message
...
You can distribute the two (*.xls and *.csv) files, but that sounds
kind
of
weird to me.

If you only have a single *.csv file, then you should do the
importing,
save
it
as a .xls file and distribute that.

If you have a *.csv file that's updated lots of times, then distribute
the
single *.xls file and then redistribute (as often as it's updated) the
*.csv
files.

If you put the procedure in its own module, you can remove the module.
Debra