Auto List Members

[Jimmy4559]

I have an Access form that I need to export to Excel; I am using Office 2000. This I can do. What I want, is to reformat the spreadsheet before e-mailing it onto its intended recepients. Two lines of code simply refuse to work:

ActiveWindow.DisplayZeros = False
ActiveWindow.DisplayGridlines = False

The error message is: "Method or data member not found"

In fact, neither of these options is available in the Auto List Members drop-down list of propeties for this control. The really annoying thing is that I have a block of code which contains these and does work. The only thing is that this code was written in an Access 97 database.

Here is the complete code:

Private Sub Command32_Click()

On Error GoTo Err_Command32_Click

Dim oXLApp As Excel.Application
Dim oXLWBook As Excel.Workbook

DoCmd.OutputTo acOutputForm, "Stock Take", _
"MicrosoftExcel(*.xls)", "C:\Stock Take.xls", False, ""

' open the workbook (it will be invisible to the user)
Set oXLWBook = Excel.Workbooks.Open("C:\Stock Take.xls", 0)
' make the first sheet the active sheet
oXLWBook.Worksheets("Stock Take&quot.Activate
' the following commands apply to the active sheet, and therefore need no prefix
Cells.Replace What:="-0", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:= False
Columns.AutoFit

ActiveWindow.DisplayZeros = False
ActiveWindow.DisplayGridlines = False

Range("A1&quot.Select

' save the changes
oXLWBook.Save
' and close
oXLWBook.Close SaveChanges:= False

' now open excel, and load the required spreadsheet
Set oXLApp = CreateObject("Excel.Application&quot
Set oXLWBook = oXLApp.Workbooks.Open("C:\Stock Take.xls", 0)
oXLApp.Visible = True

Exit_Command32_Click:
Exit Sub

Err_Command32_Click:
MsgBox Err.Description
Resume Exit_Command32_Click

End Sub

 

[MarkSweetland]

I'm not sure that ActiveWindow is available if oXLApp is not visible, but you can access the properties through the Windows collection.

oXLApp.Windows(1).DisplayGridLines=true ' Windows(1) is always ActiveWindow

or preferably

oXLApp.Windows("Stock Take.xls":1).DisplayGridLines=true

Mark

 

[Jimmy4559]

Much appreciated, Mark. I'm only just getting into working between applications - all my experience has been with Access - so I sometimes need a push. The code wouldn't work while oXLApp wasn't visible but I just tagged it on the end after the application was open - good enough for government work. Never thought to do that. I'll play with it a bit more when I have time. Suffice to say, you got me out of a hole.