runtime 438 error

[krowe]

Hi

I have a data export from Access into Excel, 3 spreadsheets are exported, a master sheet (StatCompile) opens that takes data from the exported sheets, and a 'control' spreadsheet (ctlxlsStat) opens and has some code to print x number of copies of the master spreadsheet.

I have just converted everything to office 2010 and tweaked the code to change the file extensions.

I am now getting a runtime error saying: 'object doesn't support this property or method.

I initially thought this was a references problem, but the Excel and Office 14 references are selected.

I wanted to unselect them and re-add them, but it wont let me as it says the excel one is in use.

Does anyone have any ideas please. Here is the code (it runs from an excel form in ctlxlsStat where you enter the number of copies to print btw):

Option Explicit
Dim i As Variant
Dim currentDir As String
 
Private Sub CommandButton1_Click()
currentDir = ActiveWorkbook.Path
'Workbooks.Open currentDir & "\CasesCurrent.xls"
Workbooks.Open currentDir & "\CasesOpened.xls"
Workbooks.Open currentDir & "\CasesClosed.xls"
Workbooks.Open currentDir & "\Actions.xls"
Workbooks.Open currentDir & "\StatCompile.xlsm"
[COLOR=red]Workbooks("statCompile.xlsm").Select[/COLOR]
Sheets("Summary").Select
For i = 1 To Printctl.TextBox1.Text
ActiveSheet.PrintOut
Next i
'Workbooks("CasesCurrent.xls").Close SaveChanges:=False
Workbooks("CasesOpened.xls").Close SaveChanges:=False
Workbooks("CasesClosed.xls").Close SaveChanges:=False
Workbooks("Actions.xls").Close SaveChanges:=False
'Workbooks("StatCompile.xlsm").Close SaveChanges:=True
'Kill currentDir & "\CasesCurrent.xls"
Kill currentDir & "\CasesOpened.xls"
Kill currentDir & "\CasesClosed.xls"
Kill currentDir & "\Actions.xls"
Workbooks("ctlxlsStat.xlsm").Close SaveChanges:=False
End Sub
 
Private Sub CommandButton2_Click()
currentDir = ActiveWorkbook.Path
'Kill currentDir & "\CasesCurrent.xls"
Kill currentDir & "\CasesOpened.xls"
Kill currentDir & "\CasesClosed.xls"
Kill currentDir & "\Actions.xls"
Workbooks("ctlxlsStat.xlsm").Close SaveChanges:=False
End Sub

I've highlighted the probelm line. I have tried to re-add the line and the command select doesn't automatically come up in the list as i type, indicating again that its a reference library problem, i just cant work out how to fix it.

Thanks in advance.

Kev

 

[Macropheliac]

I don't think you can select a workbook. Try omitting that and using

Workbooks("statCompile.xlsm").Sheets("Summary")

In truth, you shouldn't select at all.

 

[krowe]

Thanks for your reply, when i run that it highlights .Sheets and says compile error: invalid use of property

 

[boblarson]

Is this code in Access or Excel? If in Access you really need to read this:
http://www.btabdevelopment.com/ts/excelinstance

as I can see major problems if you are doing it from Access. Also, if you are doing this code from Access, we can help you fix it but need to know first.

 

[Macropheliac]

Is this code initiated from Access? EDIT: lol! Bob beat me to it!

 

[krowe]

Hi

sorry for the confusion, this is the code i have in Access to export the data and kick off the excel process:

Private Sub Command22_Click()
Excelpath = Environ("programfiles") & "\Microsoft Office\OFFICE14\excel.exe"
mypath = Left$(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir$(CurrentDb.Name)))
 mypath2 = ("" & Excelpath & " """ & mypath & "\ctlxlsStat.xlsm""")
    DoCmd.OutputTo acQuery, "qryCasesOpenedStats", "MicrosoftExcel(*.xlsx)", mypath & "\CasesOpened.xlsx", False, ""
    DoCmd.OutputTo acQuery, "qryCasesClosedStats", "MicrosoftExcel(*.xlsx)", mypath & "\CasesClosed.xlsx", False, ""
    DoCmd.OutputTo acQuery, "qryStatExportActions", "MicrosoftExcel(*.xlsx)", mypath & "\Actions.xlsx", False, ""
    Call Shell(mypath2)
    
End Sub

Then excel opens with a form asking how many copies to print and the code i previosuly posted runs from a button, this time within the excel file (ctlxlsStat.xlsm).

I realise this is an Access forum, but you are the best guys I know to ask about these coding issues

Thanks

Kev

 

[boblarson]

For better control you should assign the workbooks to objects.

Option Explicit
Dim i As Variant
Dim currentDir As String
 
Private Sub CommandButton1_Click()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wb3 As Workbook
Dim wb4 As Workbook

currentDir = ActiveWorkbook.Path

'Workbooks.Open currentDir & "\CasesCurrent.xls"
Set wb1 = Workbooks.Open(currentDir & "\CasesOpened.xls")
Set wb2 = Workbooks.Open(currentDir & "\CasesClosed.xls")
Set wb3 = Workbooks.Open(currentDir & "\Actions.xls")
Set wb4 = Workbooks.Open(currentDir & "\StatCompile.xlsm")
 
wb4.Activate

wb4.Sheets("Summary").Select

For i = 1 To Printctl.TextBox1.Text
     wb1.ActiveSheet.PrintOut
Next i

'Workbooks("CasesCurrent.xls").Close SaveChanges:=False
wb1.Close SaveChanges:=False
wb2.Close SaveChanges:=False
wb3.Close SaveChanges:=False
wb4.Close SaveChanges:=True

'Kill currentDir & "\CasesCurrent.xls"
Kill currentDir & "\CasesOpened.xls"
Kill currentDir & "\CasesClosed.xls"
Kill currentDir & "\Actions.xls"

Workbooks("ctlxlsStat.xlsm").Close SaveChanges:=False
 
Set wb1 = Nothing
Set wb2 = Nothing
Set wb3 = Nothing
Set wb4 = Nothing

End Sub
 
Private Sub CommandButton2_Click()
currentDir = ActiveWorkbook.Path
'Kill currentDir & "\CasesCurrent.xls"
Kill currentDir & "\CasesOpened.xls"
Kill currentDir & "\CasesClosed.xls"
Kill currentDir & "\Actions.xls"
Workbooks("ctlxlsStat.xlsm").Close SaveChanges:=False

End Sub

 

[boblarson]

But I'm confused as to why you are opening those other workbooks. What is the purpose since you aren't doing anything with them.

 

[krowe]

That new code works perfectly.

Thank you!!!

The other books contains the raw data that populates compile.xls, I will test to see if they have to be open for the links to work, i kind of assumed that they had to be open, but i may be wrong.

Will simplify things if i can leave them closed.

thanks again

Kev