Reference the data in the VBA of excel

[olatzcelaya]

Dear all,

I am doing a programm in VBA integrated in excel and I have done the database in Access, how can I reference the data in the VBA of excel? What is the function name that I have to use in the estructure: Dim data as "Name of the function"? Any help will be very appreciated.

Thanks,

Olatz

 

[PHV]

menu Data -> External data -> ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MakeItSo]

Additional to PHV: turn on your macro recorder before using these steps.


Also possible:
Add a reference to Microsoft ActiveX Data Objects,

Dim Con As ADODB.Connection
Dim ConStr As String
Dim rs
ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[full path to your mdb goes here];Persist Security Info=False"

Also possible: using dao.
Also possible: turnaround, accessing the xl from within access vba...
Also possible: transferspreadsheet method in access...
etc.

Depending on what you are actually trying to perform, there are a zillion ways.


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[MakeItSo]

Ooops, sorry, forgot two things:

A good starter to find a good solution is to search the FAQs.
A keyword search "excel access connection" over the FAQs turned up this little jewel:
How can I use VBA to get my Access into Excel?

Als the two FAQs in PHV's signature are VERY useful, especially the point about using a descriptive subject...
;-)

Cheerio,
MakeItSo

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[olatzcelaya]

Thank you very much,

Could you tell me if it is necessary to declare a function that opens the database in Access to be able to reference the variables in the VBA of excel???If so, could you give me an example, because i am trying to find them but by the moment I have not found anything.


Thanks,

Olatz

 

[MakeItSo]

Not sure I understand your question.
What variables?
In the database?
Do you mean "tables", "queries" or "records" rather than "variables"?

If so, you don't need to actually start Access and open the database via code in order to interact with the database.
You just open a channel to it with ADO, like shown above.

However, we can hardly help you if you do not get preciser, i.e. including some snippet of code you already have, or whyt you want to perform in the first place.

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell