Databases Reference
In-Depth Information
FileFilter = FileFilter & "Text Files (*.txt), *.txt,"
End Select
Return
FileOpenErr:
'If no file is chosen you end up here
strike = strike + 1
Select Case strike
Case 1
ErrorBoxMsg$ = "You will get one more chance!"
nochoice = MsgBox(ErrorBoxMsg$, vbOKOnly + vbExclamation,
ErrorBoxCaption$)
GoTo Try_Again
Case 2
ErrorBoxMsg$ = "Macro will terminate"
nochoice = MsgBox(ErrorBoxMsg$, vbOKOnly + vbExclamation,
ErrorBoxCaption$)
End
End Select
End Function
It is now possible to embed the function
within a call to the subroutine
GetPathWithDialog
to obtain the path to the data for importation via a dialog box. This
utility can be demonstrated within the VBA environment by typing the following in the immediate
(or debug) window. (Note: the entire call must be typed on one line and then hit return.)
ImportDataToWorkbook
Call ImportDataToWorkbook(ActiveWorkbook.Name,
"ImportedData",
GetPathWithDialog("csvtxtxls","c:\temp","Window
Caption")
,True,True,False,True,False)
2.6
AUTOMATICALLY SAVING FILES AND TEMPLATES
As data are gathered and analyzed, and reports are created, all this information must be stored somewhere.
Often, it is desirable to store such reports in some central repository, usually on a rules-based system.
When an automated data analysis routine creates only a single report, it is not a big deal for the user to
save such a file manually. When tens or hundreds of such reports are created, it is extraordinarily
inconvenient to have a user manually save each file to its desired location. No matter what the situation
is, it is always preferable to automate every possible aspect of any given routine. It provides consistency,
not to mention convenience. The next function will automatically save an entire Excel Workbook or an
active Worksheet to a location the user specifies, and of a type that the user indicates (Workbook, comma
delimited, text, etc.). The function will return the saved filename name as a string.
Function SaveWorksheet(ByRef wkbook$, wksht, ByVal Path$, ByVal
fname$, ByVal ftype As String) As String
Dim xlFormat&, fullpath$
'If no filename passed assume user wants date/time stamp
filename!
If fname$ = "" Then
fname$ = Format(Date$, "yyyymmdd") & "_" & Format(Time$,
"hhmmss")
