How To Query Data in Excel

[sabavno]

Hi,

I have to do the following:

There is a spreadsheet containing the following list of records:

Column A Column B
Ticket # Number of Calls
1
2
3
4
5

I need to fill out the Column B from VBA.
So, basically first I would have to find the corresponding line (say, Ticket# 3) and then on that raw, populate the Column B

What are the best approach to that?

THanks.

 

[VBAjedi]

Hi!

I'm not sure what you are asking. What is going in column "B", where is that information located, and how is it related to column "A" data (i.e. is it calculated from it, or is column "A" data a key to a database somewhere else, etc).

Please supply as much information/examples as possible!
VBAjedi

 

[sabavno]

Ok,

The Column A information is the constant information (list of ticket numbers) that is stored in the excel spreadsheet. The the following columns will be added on the weekly basis with the corresponding to the certain Ticket value.
This data will be coming from the Access database.

The example of how the spreadsheet will look like after the first two weeks

Column A Column B Column C
Ticket # Number of Calls (week1) Week 2 Calls
1 123
2 23
3 50
4 23
5 23



I need to write a routine that will populate these columns automatically.

Thanks.

 

[VBAjedi]

I'm not as familiar with automating Access. Someone else want to step in here? Thanks!
VBAjedi

 

[ETID]

If all of this data is in access already,..then you only have to link the data


in a blank excel worksheet, from the menu...

Data>get external data>new database query...follow the wizard, or select the use MS query option,...carefully select options you want on each window,...such as "read only" check boxes and what not...
then return data to ms excel.

when the data is returned, you can right click in the data area and set properies such as refresh on open


this is a live link to your access db and will reflect any changes in the access db any time you open the spreadsheet.