Opening Numerous Excel docs from Cmd Button

[jonnywakey]

Hi I am hoping someone can help me with the following.

I have set up a Cmd Button on my Access Database which opens a main Excel Spreadsheet containing graphs (Pipeline Dashboard.xls).

My problem is that I have 5 other excel spreadsheets which feed the main spreadsheet.

How would I structure the VBA to open and refresh each of the 5 documents then close them (Visible False)? and then open the main Spreadsheet.

The code I have used to open the main spread is as follows:-

Private Sub Command9_Click()

Dim objXLApp As Object
Dim objXLBook As Object
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("W:\Departmt\Continuous Improvement\Pipelines\Pipeline Dashboard.xls")
objXLApp.Application.Visible = True
End Sub

Any assistance would be gratefully received.

Thanks

Jonny

 

[ajetrumpet]

to close a workbook:

workbooks.close "name of workbook"

what do you mean by refresh the books? are you talking about a book calculation method? like the F9 key? The F2 key? If someone is to help, you need to explain a little bit more in this regard.

 

[jonnywakey]

Thanks Adam, and apologies if my requirements were a little vague.

I have around 5 excel spreadsheets which are fed by information using a database query linked to ms access database.

With imported queries from Access, the information is automatically updated each time you open the excel file; however you are prompted to enable update macros each time you open the spreadsheet.

As these 5 documents feed the main excel dashboard (Pipeline Dashboard.xls) I would like these to be opened, refreshed and closed by clicking a cmd button (On click Event) and finally opening the main spreadsheet for viewing the dashboard (Graphs).

Whats important is that when the 5 documents are refreshing, this is done in the background (Visible=False) apart from the last main document.

Hope this clarifies the situation.

Many thanks for your guidance.

Jonny

 

[ajetrumpet]

Jonny,

Unfortunately I would have to see what you have, because explaining something like this is incredibly difficult. and I apologize for saying this, but i don't have the time right now to give to the explanation and research. if you would like to upload a file or two as examples, I can see what I can do. And maybe someone else here could explain without visuals as well.