open excel and set focus

[krowe]

Hi sorry to post again, but you are all very helpful and ive almost finished by DB.

I have a button that has to export 4 queries into excel spreadsheets, and then open an excel spreadsheet that has links to the 4 exports and does some nuber crunching and produces a report.

the button code is:

Dim fullpath As String
fullpath = CurrentProject.Path
' exports expenditure
DoCmd.OutputTo acQuery, "qryExpenditureExport", "MicrosoftExcelBiff8(*.xls)", fullpath & "\Exp.xls", True, "", 0
' exports income
DoCmd.OutputTo acQuery, "qryIncomeExport", "MicrosoftExcelBiff8(*.xls)", fullpath & "\In.xls", True, "", 0
' exports priority debts
DoCmd.OutputTo acQuery, "qryPriorityDebtsExport", "MicrosoftExcelBiff8(*.xls)", fullpath & "\PRIO.xls", True, "", 0
' exports non priority debts
DoCmd.OutputTo acQuery, "qryNPDExport", "MicrosoftExcelBiff8(*.xls)", fullpath & "\NPD.xls", True, "", 0
' Access form button
Call OpenSpecific_xlFile


It calls this code to open the file:

Sub OpenSpecific_xlFile()
' Late Binding (Needs no reference set)
Dim oXL As Object
Dim oExcel As Object
Dim sfullpath As String
Dim sPath As String


' Create a new Excel instance
Set oXL = CreateObject("Excel.Application")


' Only XL 97 supports UserControl Property
On Error Resume Next
oXL.UserControl = True
On Error GoTo 0


' Full path of excel file to open
On Error GoTo ErrHandle
sfullpath = CurrentProject.Path & "\FinancialStatementTemplate.xlt"


' Open it
With oXL
.Visible = True
.Workbooks.Open (sfullpath)
End With


ErrExit:
Set oXL = Nothing
Exit Sub

ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Run it from a form button

Private Sub cmdOpenExcelFIle_Click()
' Access form button
Call OpenSpecific_xlFile



I need to then make sure that FinancialStatementTemplate is then the top window so that it can be printed.

how do i set the focus on this specific window?

Thanks in advance.

Kev

 

[boblarson]

First off, you need to move this part to the very end:

oXL.UserControl = True


once you do that, you release it from your control and the objects no longer are tied to that Excel instance, so you can't do a thing with them.

Also, you can take out this: ' Only XL 97 supports UserControl Property


because it isn't true. It WAS true back when Access 97 came out and Access 95 and Access 2.0 didn't support it. But it isn't true now - all versions from 97 to 2010 support it.

Next - about it being printed. Does the user print it or are you trying to get it to print automatically?

 

[krowe]

Hi Bob,

thanks for your reponse.

I am pretty new to VBA, so please excuse my ignorance.

When you say put that line to the end, it that the very end or within that bit of code, this is what i have now:


Sub OpenSpecific_xlFile()
' Late Binding (Needs no reference set)
Dim oXL As Object
Dim oExcel As Object
Dim sfullpath As String
Dim sPath As String


' Create a new Excel instance
Set oXL = CreateObject("Excel.Application")



On Error Resume Next

On Error GoTo 0


' Full path of excel file to open
On Error GoTo ErrHandle
sfullpath = CurrentProject.Path & "\FinancialStatementTemplate.xlt"


' Open it
With oXL
.Visible = True
.Workbooks.Open (sfullpath)
oXL.UserControl = True
End With


ErrExit:
Set oXL = Nothing
Exit Sub

ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit
End Sub

As for the printing bit, my plan was to set up a autoopen macro in excel to print and then kill the exported files. (think i can do this, but may end up back here at some point).

The problem I now have is that a message box comes up asking if i want to update the links, is there a way of automating this response (as if i clicked update).

Thanks again

Kev

 

[krowe]

Hi

I've been playing about with this today, and it appears the above problem is the least of my worries.

I now keep getting cannot read file errors when I try to update the financial statement template.

It is a known error:
http://support.microsoft.com/kb/824172

my code opens the workbooks in the correct order, however the error persists. I think it may be as the Financial Statement Template opens in a new instance of excel rather than instance that the 4 exports are opened in.

EDIT:

I've had a good think about this a realised this is very similar to another DB i have and think i will try the following on monday when i get back in:

I'll set up an excel sheet to act as a controller for the report called ctlxls with this macro:

Option Explicit
Dim i As Variant
Dim currentDir As String
Private Sub CommandButton1_Click()
currentDir = ActiveWorkbook.Path
Workbooks.Open currentDir & "\In.xls"
Workbooks.Open currentDir & "\Exp.xls"
Workbooks.Open currentDir & "\PrioDebts.xls"
Workbooks.Open currentDir & "\NonPrioDebts.xls"
Workbooks.Open currentDir & "\FinancialStatementTemplate.xls"
For i = 1 To Printctl.TextBox1.Text
ActiveSheet.PrintOut
Next i
Workbooks("In.xls").Close SaveChanges:=True
Workbooks("Exp.xls").Close SaveChanges:=False
Workbooks("PrioDebts.xls").Close SaveChanges:=False
Workbooks("NonPrioDebts.xls").Close SaveChanges:=False
Workbooks("FinancialStatementTemplate.xls").Close SaveChanges:=True
Kill currentDir & "\In.xls"
Kill currentDir & "\Exp.xls"
Kill currentDir & "\PrioDebts.xls"
Kill currentDir & "\NonPrioDebts.xls"
Workbooks("ctlxls.xls").Close SaveChanges:=False
End Sub
Private Sub CommandButton2_Click()
currentDir = ActiveWorkbook.Path
Kill currentDir & "\In.xls"
Kill currentDir & "\Exp.xls"
Kill currentDir & "\PrioDebts.xls"
Kill currentDir & "\NonPrioDebts.xls"
Workbooks("ctlxls.xls").Close SaveChanges:=False
End Sub

I will open this from code in the DB after exporting the 4 queries.

Will let you know how I get on

Kev