Hi folks,

I'm fairly experienced with Excel VBA, but I have hit a snag with a project I am currently working on. I have a large number of Excel data files which include longitudinal data in the following format:

Institution ************ Age Field of Study [other qualifiers] 1980 1981..
xxxxxxxxxx xx xx xxxxxxxxxxxx xxxxxxxxxxxxxxxxx 6 28

I have built an interface with Userforms that prompt the user for relevant input in a relevant order, including all qualifier values. What I wish to do is plug these into the "Get External Data" command under the Data drop-down menu. The following is what the macro recorder generates:

THE MACRO RECORDER:

     With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
         "ODBC;DSN=Excel Files;DBQ=H:\DATA\Faculty and Student
 Counts\Enrolment\College\Career-Technical College Enrolments by
Institution,
 Re" _
         ), Array( _
         "gistration Status and ************, Major Field and Detail Field of Study
 (FOS5), Alberta 2, 1986-1999.xls;DefaultDir=H:\DATA\Faculty an" _
         ), Array( _
         "d Student
 Counts\Enrolment\College;DriverId=790;MaxBufferSiz e=2048;PageTimeout=5;" _
         )), Destination:=Sheets("OUTPUT").Range("A30"))
         .CommandText = Array( _
         "SELECT Province.Province, Province.`Inst Code`,
 Province.Institution, Province.`Major Field`, Province.`Minor Field`,
 Province.Status, Province.************, Province.`1986`, Province.`1987`,
 Province.`1988`, " _
         , _
         "Province.`1989`, Province.`1990`, Province.`1991`,
Province.`1992`,
 Province.`1993`, Province.`1994`, Province.`1995`, Province.`1996`,
 Province.`1997`, Province.`1998`, Province.`1999`" & Chr(13) & "" &
Chr(10)
 & "FROM Province" _
         , _
         " Province" & Chr(13) & "" & Chr(10) & "WHERE
 (Province.Province='Alberta') AND (Province.`Inst Code`>24809500.0)" &
 Chr(13) & "" & Chr(10) & "ORDER BY Province.`Inst Code`" _
         )
         .Name = "ExternalData_1"
         .FieldNames = True
         .RowNumbers = False
         .FillAdjacentFormulas = False
         .PreserveFormatting = True
         .RefreshOnFileOpen = False
         .BackgroundQuery = True
         .RefreshStyle = xlInsertDeleteCells
         .SavePassword = True
         .SaveData = True
         .AdjustColumnWidth = True
         .RefreshPeriod = 0
         .PreserveColumnInfo = True
         .Refresh BackgroundQuery:=False
     End With
     Sheets("OUTPUT").Select

 THE MODIFIED SCRIPT:

[Adds the following variables: Subdirectory, DataFile(DataFileNum), CStr(InstCode), COLMajDisc, COLFOS5, RegStat, and stSex]

 Sub ExternalDataCall()

     'Fix storage format to make it look really nice (OUTPUT worksheet)
 Stop
     With
ActiveSheet.QueryTables.Add(Connection:=Array(Arra y("ODBC;DSN=Excel
 Files;DBQ=H:\DATA" & Subdirectory & DataFile(DataFileNum) &
 ";DefaultDir=H:\DATA" & Subdirectory &
 ";DriverId=790;MaxBufferSize=2048;PageTimeout=5;") ),
 Destination:=ActiveCell)
         .CommandText = Array("SELECT Province.Province, Province.`Inst
 Code`, Province.Institution, Province.`Major Field`, Province.`Minor
Field`,
 Province.Status, Province.************, Province.`1986`, Province.`1987`,
 Province.`1988`, ", "Province.`1989`, Province.`1990`, Province.`1991`,
 Province.`1992`, Province.`1993`, Province.`1994`, Province.`1995`,
 Province.`1996`, Province.`1997`, Province.`1998`, Province.`1999`" &
 Chr(13) & "" & Chr(10) & "FROM Province", " Province" & Chr(13) & "" &
 Chr(10) & "WHERE (Province.`Inst Code`=" & CStr(InstCode) & ") AND
 (Province.`Major Field`='" & COLMajDisc & "') AND (Province.`Minor
Field`='"
 & COLFOS5 & "') AND (Province.Status='" & RegStat & "') AND
(Province.************='"
 & stSex & "')" & Chr(13) & "" & Chr(10) & "ORDER BY Province.`", "Inst
 Code`")
         .Name = "Query from Excel Files_1"
         .FieldNames = True
         .RowNumbers = False
         .FillAdjacentFormulas = False
         .PreserveFormatting = True
         .RefreshOnFileOpen = False
         .BackgroundQuery = True
         .RefreshStyle = xlInsertDeleteCells
         .SavePassword = True
         .SaveData = True
         .AdjustColumnWidth = True
         .RefreshPeriod = 0
         .PreserveColumnInfo = True
         .Refresh BackgroundQuery:=False
     End With

The modified VBA script generates a run-time error 13 (type mismatch) in the With ActiveSheet.... line. This is
where I've added " & Subdirectory & DataFile(DataFileNum) & " into the generated script. The values for subdirectory and datafile(datafilenum) which are strings check out and complete the full directory and file name to be accessed externally. Looking at the syntax generated by the macro recorder, I notice that it starts with a "ODBC;DSN=...; that is, with a " within which I am trying to nest another quotation mark. Perhaps this is not the way to go about this?

The second part of this is when the query wizard gets to the filter data. I'm wondering what sort of syntax is needed to specify which variables to filter and how. I can successfully use "Get External Data" manually, but I'm puzzled about how to automate it.

I really don't want to migrate all my tables to Access (the longitudinal data format isn't a great fit), plus the application also does some calculations and charting from the extracted data. I also don't want to load in the entire data file (as opposed to the records which fit the criteria). Any ideas about this stuff?

John

John,

Since no one ever answered this, I'll throw in my 2 cents worth.

Whenever you hit a snag, such as you have with "Get External Data," toss it aside and look for something else. In the final analysis -- Do we really care how a file is read? Whatever works is the standard by which I judge solutions.

In the case below, I would --
(a) Create an array of filenames from a directory in which the files were placed.

(b) Read the data from each file into an array (if the array is going to exceed available memory -- pause as needed to process the information).

Could also use a macro to concatenate the information from multiple files into a single file, and then process the single file.

That's pretty much it.

CarlR

Sometimes quitting and finding another way is not an option.

It took me a few days but I finally managed to work out how to make this work. While the original poster has probably long since found another way or given up, I'm posting this in the hope that it will save someone else the many hours it took me.

There appears to be no valid reason for the "Type Mismatch" error, it's just a eccentricity of Excel. If you start a new line in the array after you use a variable, it tends to make it happy. Thus reformating the procedure to look like this:


With ActiveSheet.QueryTables.Add(Connection:=Array( _
    Array("ODBC;DSN=Excel Files;DBQ=H:\DATA" & Subdirectory & DataFile (DataFileNum)), _
    Array(";DefaultDir=H:\DATA" & Subdirectory), _
    Array(";DriverId=790;MaxBufferSize=2048;PageTimeou t=5;")), Destination:=ActiveCell)


should fix your "Type Mismatch" error. I hope this is of benefit to someone.