Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
curious sheet behaviour
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
curious sheet behaviour
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
OK - it's working now
Hello Nick,
It's working fine now, after implementing your suggestions. Thank you! Ward -----Original Message----- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Curious interaction W/Win Exp. | Excel Discussion (Misc queries) | |||
Curious about formula | Excel Discussion (Misc queries) | |||
Just curious | Excel Discussion (Misc queries) | |||
Just curious | Excel Discussion (Misc queries) | |||
Curious about SUMPRODUCT | Excel Worksheet Functions |