curious sheet behaviour

[ward]

Hello,

Im accessing an excel file through some Word VBA (see
below). I write some data in it.

Strangely, when the workbook is saved ("wb.save"), and I
open the excel file, no workbook is displayed! It seems
the excel file does not contain any workbook anymore...

Where has it gone? is it still there?

Ward

--- CODE in Microsoft Word module ---

Sub WriteData()

Dim wb As Excel.Workbook
Dim sh As Excel.Worksheet

Dim Rowpos As Integer
Dim filenr As Integer
Dim booknr As Integer

Dim BookName As String
Dim OldFileName As String
Dim NewFileName As String

Set wb = GetObject("c:\temp\test.xls")
Set sh = wb.Sheets(1)

For booknr = 1 To 3
BookName = GenerateRandomString(5)
For filenr = 1 To 5
OldFileName = GenerateRandomString(7)
NewFileName = GenerateRandomString(10)
Rowpos = Rowpos + 1
sh.Cells(Rowpos, 1) = booknr
sh.Cells(Rowpos, 2) = BookName
sh.Cells(Rowpos, 3) = OldFileName
sh.Cells(Rowpos, 4) = NewFileName
Next filenr
Next booknr

wb.Save

Set sh = Nothing
Set wb = Nothing

End Sub

Function GenerateRandomString(Length As Integer) As String

Dim i As Integer
Dim tStr As String

For i = 1 To Length
tStr = Chr(Int(Rnd() * 26) + 65)
GenerateRandomString = GenerateRandomString & tStr
Next i

End Function

 

[Nick Cranham]

ward,
After using your code, I see that it works, but you end up with a hidden
workbook. Open the file, then Window > Unhide and you will see it.

Exactly why it ends up hidden, I'm not sure, but there are a few
inprovements that could be made to your code.

Create a new instance of Excel for your macro to use

Set xlApp= New Excel.Application
Set wb=xlApp.Workbooks.Open("C:\test\test.xls")
.....Code
wb.Save
wb.Close < which you are not currently doing

xlApp.quit < which you are not currently doing

Whether you the xlApp visible or not is up you.

NickHK

 

[ward]

Hello Nick,

It's working fine now, after implementing your suggestions.
Thank you!

Ward