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
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