Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update of cells does not work from VB6
Hello all,
I am facing the following problem. I made an application in VB6 from which I want to convert a Excel document into HTML. The Excel document has cells that are linked to a ODBC database. So after opening the document I want all cells to be refreshed using the RefreshAll. Next I update some cells with the date and timestamp. Then I save the document as HTML. But when I look at that it has no updated cells. I tried to use the ActiveWorkbook.AcceptAllChanges but then the application wants the excel document to be unshared. I can do this by first after opening using ActiveWorkbook.ProtectSharing but then it is impossible to do the SaveAs into HTML format. See the code below. Does anyone have a clue? Thanks for the effort and time Maurice Hoeneveld Dim xlTmp As Excel.Application Dim tmpFile As String Dim buffer As String Set xlTmp = New Excel.Application Screen.MousePointer = 11 lblRESPONSE.Caption = "Updating Excel Sheet" xlTmp.Workbooks.Open ExcelFile xlTmp.Application.DisplayAlerts = False xlTmp.ActiveWorkbook.RefreshAll 'Put the update date in the screen xlTmp.ActiveSheet.Range(lstUpdate1).Select xlTmp.Selection.Font.Bold = True xlTmp.ActiveSheet.Range(lstUpdate1).FormulaR1C1 = "Last Update" xlTmp.ActiveSheet.Range(lstUpdate2).Select xlTmp.Selection.Font.Bold = True xlTmp.ActiveSheet.Range(lstUpdate2).FormulaR1C1 = "=NOW()" 'Save the sheet as HTML xlTmp.ActiveWorkbook.SaveAs FileName:=strLocal _ , FileFormat:=xlHtml, PassWord:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False 'Close the workbook xlTmp.Workbooks.Close xlTmp.Quit |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update of cells does not work from VB6
But when I look at that it has no
updated cells. If by that you mean the data hasn't been refreshed then Set all you queries so the Backgroundquery option is set to false. -- Regards, Tom Oglvy "Maurice Hoeneveld" wrote in message om... Hello all, I am facing the following problem. I made an application in VB6 from which I want to convert a Excel document into HTML. The Excel document has cells that are linked to a ODBC database. So after opening the document I want all cells to be refreshed using the RefreshAll. Next I update some cells with the date and timestamp. Then I save the document as HTML. But when I look at that it has no updated cells. I tried to use the ActiveWorkbook.AcceptAllChanges but then the application wants the excel document to be unshared. I can do this by first after opening using ActiveWorkbook.ProtectSharing but then it is impossible to do the SaveAs into HTML format. See the code below. Does anyone have a clue? Thanks for the effort and time Maurice Hoeneveld Dim xlTmp As Excel.Application Dim tmpFile As String Dim buffer As String Set xlTmp = New Excel.Application Screen.MousePointer = 11 lblRESPONSE.Caption = "Updating Excel Sheet" xlTmp.Workbooks.Open ExcelFile xlTmp.Application.DisplayAlerts = False xlTmp.ActiveWorkbook.RefreshAll 'Put the update date in the screen xlTmp.ActiveSheet.Range(lstUpdate1).Select xlTmp.Selection.Font.Bold = True xlTmp.ActiveSheet.Range(lstUpdate1).FormulaR1C1 = "Last Update" xlTmp.ActiveSheet.Range(lstUpdate2).Select xlTmp.Selection.Font.Bold = True xlTmp.ActiveSheet.Range(lstUpdate2).FormulaR1C1 = "=NOW()" 'Save the sheet as HTML xlTmp.ActiveWorkbook.SaveAs FileName:=strLocal _ , FileFormat:=xlHtml, PassWord:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False 'Close the workbook xlTmp.Workbooks.Close xlTmp.Quit |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update of cells does not work from VB6
Thanks Tom,
That solved my problem. Regards Maurice "Tom Ogilvy" wrote in message ... But when I look at that it has no updated cells. If by that you mean the data hasn't been refreshed then Set all you queries so the Backgroundquery option is set to false. -- Regards, Tom Oglvy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to update a column in a work based on another work sheet | New Users to Excel | |||
Can't get update to work | Setting up and Configuration of Excel | |||
How do I save/update work to appear in 2 seperate files? | Excel Discussion (Misc queries) | |||
Allow custom update button to work while sheet is protected | Excel Discussion (Misc queries) | |||
DO NOT update links doesn't work for me. | Excel Discussion (Misc queries) |