How to start a sub when Access has finished dumping a table

[project_man]

I have Access dumping data into an Excel file and I would like to manipulate the data (create pivots and charts via subs) as soon as Access is finished the data dump. Currently I have a button on the start sheet that prompts the user to "click it" as soon as the file has opened, but this is very lame. Any thoughts on how to start code without user input? Thanks in Advance...

 

[dk]

How is the data being retrieved from Access? Is it a normal query using Data, Get External Data or are you using VBA?

There is an event called AfterRefresh which is called once a query has finished updating. Let me know how you're getting your data (post the code if you're using VBA) and I'll show you how to achieve what you need.

 

[project_man]

Hi DK,

The data dump originates from within an Access model which uses "transferspreadsheet" and "RunApp" to dump the data into, and open, the Excel file. The Access model opens the same Excel file everytime.

Thanks

 

[DonkeyOte]

Are you running the dump from Access or from Excel?

To do the pivot etc - you could set the Macro as an Autpen()

Else - you could pick up the data from Access whilst in Excel in VB (either by calling a query/macro that sits in Access to create the dump or by running a SQL Query in the Excel Macro).

Then simply add the Pivot stuff on to the Excel Macro as part of the whole routine.

 

[DonkeyOte]

Just read the dump message - you would need to add Excel in as an Object in order to be able to call Excel procedures.

I can give you the code to call an Access Query/Command from Excel (i.e. going the other way around) then you can simply write the additional requirements into the code...

Dim obj1 As AccessObject
Dim strPath As String
Dim strDBName As String

Set appaccess1 = New Access.Application
strPath = "R:currentFinanceABT"
strFile = "Process.mdb"
strDBName = strPath & strFile
appaccess1.OpenCurrentDatabase strDBName

DoCmd.OpenQuery ("Query1")

DoCmd.TransferSpreadsheet acImport, 8, "DEPT_FILTERED_DATA", "R:currentFinanceABTProcess2.xls", True, "A1:L35000"


In your case the cmd will be the other way around - here I am running a query then importing an excel table into Access. Any help?

 

[project_man]

lasw10,

Thanks for the idea, which will be useful for other applications I have, but the issue I have is not in getting the query and the data into Excel - this part works well. What I really would like is an approach that will have code run as soon as the data dump from Access is complete. I tried Auto-open, but this runs before the data dump begins.

Basically, I am using an Access model and would like a seemless way for the user to get the functionality of Excel charts and reports by clicking a button in the Access model. The data is then dumped to Exel where my Excel file will do all of the manipulation upon completion of the data dump. I have a dream...

 

[DonkeyOte]

The only way around this is to create a Macro in Access that does this automation for you - however, if using Excel language in Access is as much a pain in the a*** as Access language in Excel then have fun! I think you'll need to set up some objects for Excel so that you can use Excel commands.

Sorry to promote others...but DBForum has an area for Access users - they may be able to help too...

Any board masters able to shed light on this - I would be interested to see the code too...

 

[project_man]

What about this...

Is it possible to execute code upon creation of a new worksheet or as soon as sheets("mysheet").visible = TRUE?

 

[dk]

On 2002-09-09 08:19, project_man wrote:
What about this...

Is it possible to execute code upon creation of a new worksheet or as soon as sheets("mysheet").visible = TRUE?

Yes. Open the VB editor in Excel and press Ctrl R to make the project explorer visible. Double click the ThisWorkbook icon and use something like this.


Private Sub Workbook_NewSheet(ByVal Sh As Object)
'You can run code here to work on the sheet e.g.
Sh.Name = "My new sheet"
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'You can run code here to respond to a sheet being activated e.g.
MsgBox "Sheet """ & Sh.Name & """ has been activated."
End Sub

Any help?

 

[project_man]

I was able to use Dan's suggestion to get what I needed - thanks!!

I also think that lasw10's suggestion to open the Excel file from Access, but have a sub called from Auto-open that pulled the query info into Excel I could have made that work too.

Good suggestions all. Thanks.