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

[SkumarSS]

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

 

[dan_l]

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.

 

[FS:CH]

Hi SkumarSS,

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

#1 VBA site for analysts. #sql #import data | PlsFixThx

Work smarter and automate your tasks. Free download file. Learn how to connect to SQL tables and send data back and fourth. Both import to excel, as well as upload from excel to your sql table.

www.pls-fix-thx.com

/F