Databases Reference
In-Depth Information
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = TabDelimiter
.TextFileSemicolonDelimiter = SemicolonDelimiter
.TextFileCommaDelimiter = CommaDelimiter
.TextFileSpaceDelimiter = SpaceDelimiter
.TextFileColumnDataTypes = Array(CDT, CDT, CDT, CDT, CDT,
CDT, CDT, CDT, _
CDT, CDT, CDT, CDT, CDT, CDT, CDT, CDT, CDT, CDT, CDT,
CDT, CDT, CDT, _
CDT, CDT, CDT, CDT)
.Refresh BackgroundQuery:=False
End With
ActiveSheet.Name = NewSheetName$
End Sub
As with any subroutine, this one is a compromise between flexibility and length. The
Workbook to which the data is to be imported to must be specified, along with a sheet name to
be given to the new sheet that will be created to hold the imported data. The full path to the data
file to be imported must also be specified, along with the data type of the columns to be imported.
In this instance, the first 26 columns (A-Z) have their type specified as either (1) general or (2)
text. This is set within the subroutine by means of the
variable (
C
olumn
D
ata
Ty
pe). In
addition, up to four delimiters can be specified for the separation of data. They are tab, semicolon,
comma, and space.
If the path to the data for importation is not readily known, it would be advantageous to make
use of a dialog box to obtain the path to the data to be imported. The next function returns the
path a user selects from a dialog box as a string.
CDT
Function GetPathWithDialog(ByVal filters$, ByVal defaultdir$,
ByVal WindowCaption$) As String
'Function to Load files of type specified with Dialog Box
'Returns the name of the Workbook Loaded
Dim vFileName As String
Dim nochoice As Integer
