Update of cells does not work from VB6

  • Thread starter Thread starter Maurice Hoeneveld
  • Start date Start date
M

Maurice Hoeneveld

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
 
But when I look at that it has no
updated cells.
Click to expand...

If by that you mean the data hasn't been refreshed then

Set all you queries so the Backgroundquery option is set to false.
 
Back
Top