I'm not sure I understand your problem. Do you wan to only display the files
in a certain folder? You can use a listbox and manually fill the list box
with the items in one folder. I believe if you use a win32 object you can
lock the dialog to display only one folder.

If you want to set a default folder then use the filedialog method instead
of GetOpenFilename. See VBA help

"Paul H" wrote:



Shane, will you show me what to specify if my files are in C:\aaa but I
cannot change from the folder where I am running my programs? Your help
explanation is not clear. My actual path will be much more complex,
involving a mapped drive, but the C:\aaa example should set me right.

"Shane Devenshire" wrote in
message ...
Hi,

I don't have time to try to follow all the earlier stuff in this thread,
however the line

myFileName = Application.GetOpenFilename("CSV Files, *.CSV",, "Pick a File")

gets both the file name and path, so you just put myFileName in where ever
you need it.

As you can see I made a change to this line, not because it affect anything
it just makes it easier to display on one line here. It also shows that the
argument name is not technically necessary, in which case you must put the
arguments in the correct order.

Here is the Help explanation:

Displays the standard Open dialog box and gets a file name from the user
without actually opening any files.

expression.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText,
MultiSelect)
expression Required. An expression that returns an Application object.

FileFilter Optional Variant. A string specifying file filtering criteria.

This string consists of pairs of file filter strings followed by the MS-DOS
wildcard file filter specification, with each part and each pair separated
by
commas. Each separate pair is listed in the Files of type drop-down list
box.
For example, the following string specifies two file filters€” text and
addin:
"Text Files (*.txt),*.txt,Add-In Files (*.xla),*.xla".

To use multiple MS-DOS wildcard expressions for a single file filter type,
separate the wildcard expressions with semicolons; for example, "Visual
Basic
Files (*.bas; *.txt),*.bas;*.txt".

If omitted, this argument defaults to "All Files (*.*),*.*".

FilterIndex Optional Variant. Specifies the index numbers of the default
file filtering criteria, from 1 to the number of filters specified in
FileFilter. If this argument is omitted or greater than the number of
filters
present, the first file filter is used.

Title Optional Variant. Specifies the title of the dialog box. If this
argument is omitted, the title is "Open."

ButtonText Optional Variant. Macintosh only.

MultiSelect Optional Variant. True to allow multiple file names to be
selected. False to allow only one file name to be selected. The default
value
is False

Remarks
This method returns the selected file name or the name entered by the user.
The returned name may include a path specification. If MultiSelect is True,
the return value is an array of the selected file names (even if only one
filename is selected). Returns False if the user cancels the dialog box.

This method may change the current drive or folder.

Example
This example displays the Open dialog box, with the file filter set to text
files. If the user chooses a file name, the code displays that file name in
a
message box.

fileToOpen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")
If fileToOpen < False Then
MsgBox "Open " & fileToOpen
End If



--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"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