• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to use 2 excel file in the same sql in vba

SkumarSS

New Member
Though earlier I have worked on VB + sql, i am

new to VBA. Macro in an Excel file, need to

read data from source excel file based on some

criteria and write to output excel file.

Opened SourceFile using ADODB connection

and recordset and SQL Query

Giving sample scene -
Source file (Name, Age, CityCode).
Criteria Age <=50.
Output file currently (Name, age, citycode)
Sql = "Select Name, Age, CityCode From

SourceFile Where Age <= 50 Order By

CityCode"

This is working fine.

Would like to get CityName from CityMaster

(CityCode, CityName) and update OutputFile

with Name, Age, CityName (instead of

CityCode),
SQL = Select SourceFile.Name, SourceFile.Age,

CityMaster.CityName From SoureceFile,

CityMaster Where SourceFile.CityCode =

CityMaster.CityCode And SourceFile.Age <= 50

Order By CityCode"

This is shown error on SQL.

Request you to guide - should I open

CityMaster also along with Sourcefile to run the

above query. How to keep 2 excel files open

Need guidance and request you to provide the

code for runnng this query on VBA.

- Siva
skumar.ss@gmail.com
 
I'm not following.

If you're trying to join data between "source file" and "city master", I guess I would probably create two recordsets and use a temp table or maybe recordset.fields.append to bluff a join.
 
Hi SkumarSS,

Please find a SQL query example here prepped with the workbook. Just tweak the query string to what you wish.

/F
 
Back
Top