Databases Reference
In-Depth Information
'Debug.Print "File Doesn't Exist"
End If
End Function
2.5
IMPORTING DATA TO A WORKSHEET
There will be occasions when it is desirable to add data to an existing Workbook rather than opening
a new Workbook or Worksheet to access the data. This is especially true when compiling data for
a report that ideally would be saved as a single Workbook. This can be accomplished manually by
making the following selections from the Excel menu:
D
ata -> Get External
D
ata -> Import
Te
xt
File. This manual process can be automated by means of the VBA subroutine ImportDataToWork-
book, the code for which is as follows.
Sub ImportDataToWorkbook(Workbook$, NewSheetName$, fullpath$, _
ByVal ColumnTypeText As Boolean, _
ByVal TabDelimiter As Boolean, ByVal SemicolonDelimiter As
Boolean, _
ByVal CommaDelimiter As Boolean, ByVal SpaceDelimiter As
Boolean)
'This Subroutine will import data to an exisiting Workbook
'Workbook$ is the name of the Workbook to receive to data
'A new sheet named NewSheetName$ will be created to store the
data
'fullpath$ is the full path (path + filename) to the data to
be imported
'Columns Can be set to Type Text (True) or General (False)
for Cols A-Z
'Up to four delimiters can be specified
Dim Name$, ConnPath$, CDT As Integer
Call ActivateWorkbook(Workbook$)
'Set Up Properties and Methods
ConnPath$ = "TEXT;" & fullpath$
Name$ = Extract_FileName(fullpath$, False)
Select Case ColumnTypeText
Case True
CDT = 2
Case False
CDT = 1
End Select
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:=ConnPath$,
Destination:=Range("A1"))
.Name = Name$
.FieldNames = True
.RowNumbers = False
