using excel vba to extract figures from access report (1 Viewer)

omer104

New member
Local time
Today, 03:26
Joined
Feb 19, 2019
Messages
4
Hi guys please can someone help

I am trying to querying a access report via excel VBA
Normally in Access i enter start and end date (normally the same date)
then press run, and export it in pdf or excel, (R_UCITS-AIFM_Summary)

I am trying to do this just via excel, can someone help I am getting automation errors, I think the issue relates to there is a password on the database/I need to enter dates in my excel spreadsheet and refer this to VBA code, just unsure where to start,

Here is my code so far,


Sub getdatafromaccess()

Dim DBFullname As String
Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recorder As ADODB.Recordset
Dim Col As Integer
Dim pword As String

Cells.Clear

pword = "Arch"

DBFullname = "Z:\Portfolio Analysis\Exposure
Monitoring\PAS_ACCESS.accdb"

Set Connection = New ADODB.Connection
Connect = "Provider=Microsoft.ACE.OLEDB.12.0;"
Connect = Connect & "Data source=" & DBFullname & ";" & "Jet OLEDB:Database Password" = pword
Connection.Open ConnectionString:=Connect

Set Recordset = New ADODB.Recordset
With Recordset

Source = "Select * from R_UCITS-AIFM_Summary"
.Open Source = Source, Connection, ActiveConnection:=Connection

For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next

Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing

End Sub
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:26
Joined
Feb 28, 2001
Messages
26,996
What I don't understand is, if you have to trigger this report from Access before you can do this analysis, why don't you write a query that looks similar to the one driving the report and just directly look up the answers IN ACCESS? This "export to Excel" followed by "pick up using Excel" method seems unusually convoluted. After all, the info you want has to exist in Access or you wouldn't be looking at the report it generated?

Try telling us the problem you want to solve IN WORDS, not in code.
 

omer104

New member
Local time
Today, 03:26
Joined
Feb 19, 2019
Messages
4
a excel macro VBA that extracts R_UCITS-AIFM_Summary report that is in Access, into excel?
 

omer104

New member
Local time
Today, 03:26
Joined
Feb 19, 2019
Messages
4
1. Open Access
2. Click on R_UCITS-AIFM_Summary report
3. Enter dates
4.run and export to excel

I would like to do this all through vba can it be done?
 

omer104

New member
Local time
Today, 03:26
Joined
Feb 19, 2019
Messages
4
I’m sorry but am I not allowed to this?
Mr excel one was the original one I posted but I noticed few hours later there was a more active one for vba access here?
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:26
Joined
Sep 21, 2011
Messages
14,038
I’m sorry but am I not allowed to this?
Mr excel one was the original one I posted but I noticed few hours later there was a more active one for vba access here?


No, it is allowed, but it is considered common courtesy to advise posters on the site that is it crossposted elsewhere (with a link).That generally applies to most forums.


This is to allow any repliers to your post to be aware of what has already been suggested/offered and prevent the same advice being duplicated.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:26
Joined
Feb 28, 2001
Messages
26,996
1. Open Access
2. Click on R_UCITS-AIFM_Summary report
3. Enter dates
4.run and export to excel

I would like to do this all through vba can it be done?

Obviously, Step 1 must be done manually because Access VBA isn't available until after you run Access and give it a context in which to operate.
Step 2 is doable via DoCmd.OutputTo for a report object.
Step 3 (when using VBA) must be done ahead of time in some other way because VBA is the wrong answer for data entry of this type.
Step 4 (export to Excel) is an option in the .OpenReport where you choose the type of file you are building.

Normally, if you are building a report, it is based on a query (literal or named) .RecordSource in the report's definition. In the case that you describe, you would put your dates (described in your step 3) as parameters to the query being used as a .RecordSource and THEN open the report with the Export to Excel option. And you would have to supply a name for the output file - see the link I have included.

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.outputto
 

Users who are viewing this thread

Top Bottom