Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 12th, 2003, 03:47 PM
Registered User
 
Join Date: Jun 2003
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default VBA programming of "Get External Data"

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
 
Old August 19th, 2003, 07:45 PM
Authorized User
 
Join Date: Aug 2003
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old August 2nd, 2005, 10:02 PM
Registered User
 
Join Date: Aug 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Referencing data on external drive startfarm ASP.NET 2.0 Basics 4 June 9th, 2008 06:20 PM
Database, VBA programming camehere Access 11 March 5th, 2008 08:57 AM
VBA-API form filler for external application Dmitri_h Excel VBA 1 January 30th, 2007 04:28 AM
External db files to Excel, via VBA automation ?? cipher_nb Excel VBA 1 December 13th, 2004 04:56 AM
Read external data rajanikrishna Classic ASP Databases 0 May 6th, 2004 03:24 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.