Hidden xls spreadsheet

  Nigel-331402 14:51 20 Sep 06
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 06

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 06

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 06

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 06

No problems, any time.

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

What is Amazon Go and will it come to the UK? The store without checkouts or queues

1995-2015: How technology has changed the world in 20 years

Hands-on with the Star Wars fighting drones you can fly yourself

15 macOS Sierra tips | How to use macOS Sierra: Secret tricks and best new features in Apple's new…