Hidden xls spreadsheet

  Nigel-331402 14:51 20 Sep 2006
Locked

I'm trying to create an XLS spreadsheet using VBA from Outlook.

A code snippet that recreates the problem is shown here:

Sub test()
' get spreadsheet object
Set ExcelWorksheet = GetObject("c:\test.xls")
' write sample data to sheet
For i = 1 To 10
ExcelWorksheet.worksheets(1).cells(i, 1) = 3 * i
Next
' check data to ensure it's been written
For i = 10 To 1 Step -1
j = ExcelWorksheet.worksheets(1).cells(i, 1).Value
Next
' tidy up
ExcelWorksheet.Save
ExcelWorksheet.Close
Set ExcelWorksheet = Nothing
End Sub


The code runs ok but when I open the spreadsheet up it doesn't seem to open. I have to go to "Tools" > "Unhide" to make the sheet visible.

Why is the spreadsheet being hidden and what can I do to stop it ?

If I create a new spreadsheet and run the above code it works ok but if I rerun it I get the above problem.

I'm using XP Professional with all the latest updates and Office 2003.

  silverous 16:09 20 Sep 2006

I don't tend to use GetObject when working with Excel via VBA, maybe that's the issue.

Your excelworksheet variable is actually pointing to a "workbook" I believe, this isn't an issue, just a naming issue.

Try this:

Sub test()

' get Excel
Set objXLApp = CreateObject("Excel.Application")

' add workbook
objXLApp.Workbooks.Add
Set ExcelWorksheet = objXLApp.ActiveWorkbook

' write sample data to sheet
For i = 1 To 10
ExcelWorksheet.worksheets(1).cells(i, 1) = 3 * i
Next
' check data to ensure it's been written
For i = 10 To 1 Step -1
j = ExcelWorksheet.worksheets(1).cells(i, 1).Value
Next
' tidy up
ExcelWorksheet.SaveAs "C:\temp.xls"
ExcelWorksheet.Close
Set ExcelWorksheet = Nothing
End Sub

  silverous 16:10 20 Sep 2006

Note: you might hit an issue if you want it to repeatedly save over the sheet or to add to an existing sheet - let me know how it is supposed to work when it is has been re-run and I'll help further.

  Nigel-331402 10:27 21 Sep 2006

Silverous

By changing the way the spreadsheet is accessed seems to have sured the problem. I've changed your .Add to a .Open and the .SaveAs to a .Save as I needed to update an existing sheet.

Many thanks for your help.

  silverous 12:40 21 Sep 2006

No problems, any time.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?