Close 2 of 3 excel workbooks from access 1

[loydlr]

I have 3 excel files:
PriceList.xls
A template file linked to the following 2 excel files. Basically a very large printable pricelist updated from linked excel files exported from access. Protected from modifying cell contents.

TotalCost.xls & Multiplier.xls
Files transferred from access queries. So the user can set retail prices in the nicely formatted pricelist.xls file.

I have been unable to update the links without having all 3 excel files open. Updating works perfectly if I open the 2 linked files prior to opening PriceList.xls. I have a button on a switchboard that opens all 3 files. Updating occurs perfectly, but I want to automate the process so the user only ever sees the updated TotalCost.xls.

I need to:
Open TotalCost.xls (invisible)
Open Multiplier.xls (invisible)
Open PriceList.xls (visible)
[links update]
Close TotalCost.xls
Close Multiplier.xls

My problem is that I don't know how to close a single instance of excel from access vb. Thanks...

 

[PHV]

What have you so far ?
Instantiate an excel object, open the 3 workbooks while still invisible, update your pricelist, close the 2 unwanted workbooks and then make excel visible.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[loydlr]

Sorry to take so long getting back...

Dim appExcel As New Excel.Application
Dim strPath, strPath1, strPath2 As String

appExcel.Application.DisplayAlerts = False

strPath = "C:\TTDDB\Pricebook\Multiplier.xls"
strPath1 = "C:\TTDDB\Pricebook\TotalCost.xls"
strPath2 = "C:\TTDDB\Pricebook\PriceList.xls"

appExcel.Visible = False
appExcel.Workbooks.Open strPath
appExcel.Workbooks.Open strPath1
appExcel.Workbooks.Open strPath2

Set appExcel = Nothing

This is all I have. I don't have any clue as to how to manipulate individual workbooks from within access. I need to be able to open the 3 workbooks as invisible then set PriiceList.xls to visible, then close Multiplier.xls and TotalCost.xls.

 

[loydlr]

I've managed to do what I set out to do. I am having a problem though. The code executes perfectly only every other time. I get the following error every other time the code executes.

Run-time error 9 Subscript out of range
It occurs on:
Workbooks("Multiplier.XLS").Close SaveChanges:=False
Workbooks("TotalCost.XLS").Close SaveChanges:=False

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Private Sub cmdCreatePriceList_Click()
On Error GoTo Err_cmdCreatePriceList_Click

Dim appExcel As New Excel.Application
Dim strPath, strPath1, strPath2 As String

strPath = "C:\TTDDB\Pricebook\Multiplier.xls"
strPath1 = "C:\TTDDB\Pricebook\TotalCost.xls"
strPath2 = "C:\TTDDB\Pricebook\PriceList.xls"

Kill "C:\TTDDB\Pricebook\Multiplier.xls"
Kill "C:\TTDDB\Pricebook\TotalCost.xls"

DoCmd.RunMacro "ExportDlrCost"

appExcel.Application.DisplayAlerts = False

appExcel.Visible = False
appExcel.Workbooks.Open strPath
appExcel.Workbooks.Open strPath1
appExcel.Workbooks.Open strPath2

Workbooks("Multiplier.XLS").Close SaveChanges:=False
Workbooks("TotalCost.XLS").Close SaveChanges:=False

appExcel.Visible = True
appExcel.Application.DisplayAlerts = True
Set appExcel = Nothing

Exit_cmdCreatePriceList_Click:
Exit Sub

Err_cmdCreatePriceList_Click:
MsgBox Err.Description
Resume Exit_cmdCreatePriceList_Click

End Sub

I then have to use task manager to close the invisible excel files left open. I'm not sure what I'm doing wrong, but the error is consistent. Every other time I execute the code. Any ideas?

 

[PHV]

Try something like this:
Dim appExcel As New Excel.Application
Dim strPath As String, strPath1 As String, strPath2 As String
Dim wb As Excel.Workbook, wb1 As Excel.Workbook
appExcel.DisplayAlerts = False
strPath = "C:\TTDDB\Pricebook\Multiplier.xls"
strPath1 = "C:\TTDDB\Pricebook\TotalCost.xls"
strPath2 = "C:\TTDDB\Pricebook\PriceList.xls"
appExcel.Visible = False
Set wb = appExcel.Workbooks.Open(strPath)
Set wb1 = appExcel.Workbooks.Open(strPath1)
appExcel.Workbooks.Open strPath2, 3 ' Update links
DoEvents
wb.Close True
wb1.Close True
appExcel.Visible = True
Set appExcel = Nothing

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[loydlr]

Works like a charm. Thanks PHV...