Pulling all info from open Access subform into excel

0
votes

We are using a access front end that is being developed externally. It displays a form that contains numerous points of data and a few subforms.

I am trying to code a excel sheet that would pull data from various places in the currently open form.

I have gotten as far as to be able to access all the points of data in the main form and the first line in the subforms using the following code (In this example, the "pnum", is the left most field displayed in the subform):

Set objacc = GetObject("xxxxx\Database.accdb").Application

Debug.Print objacc.Forms("mainform").Controls("main subform").Controls("Pnumber")

This works and gets me the value of the very first element named "Pnumber" in the main subform.

However, the way the subform is formulated, it can have anywhere between 1 and 30+ "Pnumber" fields.

I need a way to pull everything that the currently visible (filtered down) subform contains regardless of how many lines there are.

Thanks!

1
excelvbams-access

1 Answers

0
votes

If I were the developer of that Access application, and you asked me for this feature, I would code an API for you to automate getting the right data.

One approach I can think of in this particular case would be to populate some temporary tables, on request from Excel VBA, and have Excel use the Access database file as an external source of data. Or Access could be coded to push the right data into the open Excel worksheet.

The way you would get access to the data in its current state in the form (filtered, sorted, etc.) would probably be best through the RecordsetClone property of that (sub)form. At least that's what I imagine I would use to implement that feature.