I am also having a problem with Formulas not Automatically Calculating when cells are updated.
The odd thing is the cells with formulas appear to be updated just fine when the sheet is saved as a PDF, as the PDF shows the cells with correct values. But when I open the spreadsheet in Office 365, the cells with formulas are not updating. When the spreadsheet is open in Office 365, if i double-click a cell that has a formula, and then simply click another cell, then the affected cell will update correctly.
In attached image, the open spreadsheet (which is opened, after the PDF was saved) shows Cells that are not updating, but the saved PDF shows them updated.
- 2019-09-04_6-28-16.jpg (431.17 KiB) Viewed 1760 times
attached is the Spreadsheet before it is updated by code.
attached is the Spreadsheet after it is updated by code.
See Sheet "2019 - DATA", cells I-10, I-26, I-28, I-32
and several Cells in Sheet "8-19", which are seen in the attached image.
My Office Version: Microsoft Office 365 ProPlus Version 1902.
Code below. Note: I was using XLS version 9.7, but I downloaded and tested XLS version 9.9 and I am getting the same results.
Public Sub WorkbookUpdate()
Dim workbook As Workbook '
Dim res As Integer
Dim sb As StringBuilder
Dim SpreadsheetPath As String
Dim CellColumn As Short
Dim CellBlank As Boolean
Dim mma_line As String
Dim CSVRowsArray() As String
Dim Pmin() As String
Dim Pmax() As String
Dim Pavg() As String
Dim Ptot() As String
Dim Dmin() As String
Dim Dmax() As String
Dim Davg() As String
Dim Dtot() As String
Dim Fmin() As String
Dim Fmax() As String
Dim Favg() As String
Dim Ftot() As String
Dim Fpeak() As String
Dim KWmin() As String
Dim KWmax() As String
Dim KWavg() As String
Dim KWtot() As String
Dim KWHmin() As String
Dim KWHmax() As String
Dim KWHavg() As String
Dim KWHtot() As String
Dim PYear As String
Dim PdateShrt As String
Dim PdateShrt2 As String
Dim PdateNorm As String
Dim MnthStr As String
Dim PDFname As String
Dim ContinueWithWorkbook As Boolean
sb = New StringBuilder(100)
ContinueWithWorkbook = True
PDFname = vbNullString
Try
'Read the INI file, to get the Spreadsheet name
res = GetPrivateProfileString("OperData", "ExcelWorkBookPath", "", sb, sb.Capacity, MyAppPath & "ManagAIR-Net.ini")
If sb.Length > 0 Then
If InStr(sb.ToString, ".xlsx") Then
PDFname = sb.ToString.Replace(".xlsx", ".pdf")
Else
FrmLabelErrMess2.LabelErrMess.Text = Err.Description & " Workbook Error Pnt1, " & DateTime.Now.ToString()
Call EncrFileRout(CStr("MAFileBU\BackUpFrm\WorkbookUpdate Error Pnt1, " & Err.Description))
ContinueWithWorkbook = False
End If
Else
FrmLabelErrMess2.LabelErrMess.Text = Err.Description & " Workbook Error Pnt2, " & DateTime.Now.ToString()
Call EncrFileRout(CStr("MAFileBU\BackUpFrm\WorkbookUpdate Error Pnt2, " & Err.Description))
ContinueWithWorkbook = False
End If
If ContinueWithWorkbook Then
workbook = New Workbook()
PYear = Year(Now.AddMonths(-1))
PdateShrt = Now.AddMonths(-1).ToString("M-yy")
PdateShrt2 = Now.AddMonths(-1).ToString("MM-yy")
PdateNorm = Now.AddMonths(-1).ToString("MM-yyyy")
MnthStr = MonthName(Month(Now.AddMonths(-1)), True).ToUpper
'Read in the Data from the .csv file
Try
' Open the file using a stream reader.
Using sr As New StreamReader(CStr(MyAppPath & "TextData\" & MnthStr & "\" & MnthStr & " " & PYear & " mma.csv"))
' Read the stream to a string.
mma_line = sr.ReadToEnd()
'Split the Data from the String
CSVRowsArray = mma_line.Split(vbCrLf)
'Verify that all the data was in the csv file.
If CSVRowsArray.Length = 21 Then
Pmin = CSVRowsArray(0).Split(",")
Pmax = CSVRowsArray(1).Split(",")
Pavg = CSVRowsArray(2).Split(",")
Ptot = CSVRowsArray(3).Split(",")
Dmin = CSVRowsArray(4).Split(",")
Dmax = CSVRowsArray(5).Split(",")
Davg = CSVRowsArray(6).Split(",")
Dtot = CSVRowsArray(7).Split(",")
Fmin = CSVRowsArray(8).Split(",")
Fmax = CSVRowsArray(9).Split(",")
Favg = CSVRowsArray(10).Split(",")
Ftot = CSVRowsArray(11).Split(",")
Fpeak = CSVRowsArray(12).Split(",")
KWmin = CSVRowsArray(13).Split(",")
KWmax = CSVRowsArray(14).Split(",")
KWavg = CSVRowsArray(15).Split(",")
KWtot = CSVRowsArray(16).Split(",")
KWHmin = CSVRowsArray(17).Split(",")
KWHmax = CSVRowsArray(18).Split(",")
KWHavg = CSVRowsArray(19).Split(",")
KWHtot = CSVRowsArray(20).Split(",")
'Load workbook from disk.
SpreadsheetPath = CStr(MyAppPath & "TextData\" & PYear & " - " & sb.ToString())
If File.Exists(SpreadsheetPath) Then
Try
workbook.LoadFromFile(SpreadsheetPath)
'Initailize worksheet
Dim SheetName As String = PYear & " - DATA"
Dim sheet As Worksheet = workbook.Worksheets.Item(SheetName)
'Examples for writing to Cells.
'Writes number
'sheet.Range("L7").NumberValue = 89
'Writes string
'sheet.Range("L10").Text = "Hello World"
'Writes date
'sheet.Range("L11").DateTimeValue = System.DateTime.Now
'Writes formula
'sheet.Range("L12").Formula = "=1111*11111"
'Set the Column to write to based on the Month.
CellColumn = (Month(Now.AddMonths(-1)) + 1)
'Check to see if the Cell is Blank.
CellBlank = sheet.Range(7, CellColumn).IsBlank
'Write Value to appropriate Cell
If CellBlank = True Then
sheet.Range(7, CellColumn).NumberValue = Pmin(0) 'Minimum Pressure
'If (Pmin(1) <> "") And (Pmin(1) <> "NA") Then
' sheet.Range(7, CellColumn).Comment.Text = Pmin(1) 'TimeStamp of Minimum Pressure
' sheet.Range(7, CellColumn).Comment.Height = 45
'End If
End If
'Check to see if the Cell is Blank.
CellBlank = sheet.Range(8, CellColumn).IsBlank
'Write Value to appropriate Cell
If CellBlank = True Then sheet.Range(8, CellColumn).NumberValue = Pmax(0) 'Maximum Pressure
'Check to see if the Cell is Blank.
CellBlank = sheet.Range(9, CellColumn).IsBlank
'Write Value to appropriate Cell
If CellBlank = True Then sheet.Range(9, CellColumn).NumberValue = Pavg(0) 'Average Pressure
'Check to see if the Cell is Blank.
CellBlank = sheet.Range(13, CellColumn).IsBlank
'Write Value to appropriate Cell
If CellBlank = True Then sheet.Range(13, CellColumn).NumberValue = Dmin(0) 'Minimum DewPoint
'Check to see if the Cell is Blank.
CellBlank = sheet.Range(14, CellColumn).IsBlank
'Write Value to appropriate Cell
If CellBlank = True Then
sheet.Range(14, CellColumn).NumberValue = Dmax(0) 'Maximum DewPoint
'If (Dmax(1) <> "") And (Dmax(1) <> "NA") Then
' sheet.Range(14, CellColumn).Comment.Text = Dmax(1) 'TimeStamp of Maximum DewPoint
' sheet.Range(14, CellColumn).Comment.Height = 45
'End If
End If
'Check to see if the Cell is Blank.
CellBlank = sheet.Range(15, CellColumn).IsBlank
'Write Value to appropriate Cell
If CellBlank = True Then sheet.Range(15, CellColumn).NumberValue = Davg(0) 'Average DewPoint
'Check to see if the Cell is Blank.
CellBlank = sheet.Range(19, CellColumn).IsBlank
'Write Value to appropriate Cell
If CellBlank = True Then sheet.Range(19, CellColumn).NumberValue = Fmin(0) 'Minimum Flowrate
'Check to see if the Cell is Blank.
CellBlank = sheet.Range(20, CellColumn).IsBlank
'Write Value to appropriate Cell
If CellBlank = True Then
sheet.Range(20, CellColumn).NumberValue = Fmax(0) 'Maximum Flowrate
'If (Fmax(1) <> "") And (Fmax(1) <> "NA") Then
' sheet.Range(20, CellColumn).Comment.Text = Fmax(1) 'TimeStamp of Maximum Flowrate
' sheet.Range(20, CellColumn).Comment.Height = 45
'End If
End If
'Check to see if the Cell is Blank.
CellBlank = sheet.Range(21, CellColumn).IsBlank
'Write Value to appropriate Cell
If CellBlank = True Then sheet.Range(21, CellColumn).NumberValue = Favg(0) 'Average Flowrate
'Check to see if the Cell is Blank.
CellBlank = sheet.Range(24, CellColumn).IsBlank
'Write Value to appropriate Cell
If CellBlank = True Then sheet.Range(24, CellColumn).NumberValue = Ftot(0) 'MCF, Flow Total
'Check to see if the Cell is Blank.
CellBlank = sheet.Range(25, CellColumn).IsBlank
'Write Value to appropriate Cell
If CellBlank = True Then sheet.Range(25, CellColumn).NumberValue = Fpeak(0) 'Average of 8 hourly Peaks
'Check to see if the Cell is Blank.
CellBlank = sheet.Range(27, CellColumn).IsBlank
'Write Value to appropriate Cell
If CellBlank = True Then sheet.Range(27, CellColumn).NumberValue = KWHtot(0) 'KWH
workbook.Save()
Catch excptn As Exception
FrmLabelErrMess2.LabelErrMess.Text = Err.Description & " Workbook Error Pnt3, " & DateTime.Now.ToString()
Call EncrFileRout(CStr("MAFileBU\BackUpFrm\WorkbookUpdate Error Pnt3, " & Err.Description))
ContinueWithWorkbook = False
End Try
If ContinueWithWorkbook Then
If Not String.IsNullOrEmpty(PDFname) Then
'Load workbook from disk.
SpreadsheetPath = CStr(MyAppPath & "TextData\" & PYear & " - " & sb.ToString())
If File.Exists(CStr(MyAppPath & "TextData\" & PdateShrt2 & " - " & PDFname)) Then
'Do Nothing, PDF file already created.
Else
Try
workbook.LoadFromFile(SpreadsheetPath)
'Initailize worksheet
Dim SheetName2 As String = PdateShrt
Dim sheet2 As Worksheet = workbook.Worksheets.Item(SheetName2)
sheet2.SaveToPdf(CStr(MyAppPath & "TextData\" & PdateShrt2 & " - " & PDFname))
Catch excptn As Exception
FrmLabelErrMess2.LabelErrMess.Text = Err.Description & " Workbook Error Pnt4, " & DateTime.Now.ToString()
Call EncrFileRout(CStr("MAFileBU\BackUpFrm\WorkbookUpdate Error Pnt4, " & Err.Description))
ContinueWithWorkbook = False
End Try
End If
End If
End If
If ContinueWithWorkbook Then
If Not String.IsNullOrEmpty(PdateNorm) Then
'Convert 15 Min Avg CSV file to Excel File
If File.Exists(CStr(MyAppPath & "TextData\" & PdateNorm & " 15MinAvg.xlsx")) Then
'Do Nothing, xlsx file already created.
Else
Try
'Import the csv data into excel.
workbook.LoadFromFile(MyAppPath & "TextData\" & MnthStr & "\" & PdateNorm & " 15MinAvg.csv", ", ", 1, 1, ExcelVersion.Version2010)
'Name the Worksheet
Dim sheet As Worksheet = workbook.Worksheets(0)
sheet.Name = PdateNorm & " 15MinAvg"
'Format the Number cells.
sheet.Range(2, 2, sheet.LastRow, 5).NumberFormat = "####0"
sheet.Range(2, 6, sheet.LastRow, 11).NumberFormat = "####0.00"
sheet.Range(2, 12, sheet.LastRow, 17).NumberFormat = "####0"
sheet.Range(2, 2, sheet.LastRow, sheet.LastColumn).IgnoreErrorOptions = IgnoreErrorType.NumberAsText
'Set Conditional background formatting for low pressure, high dewpoints, and high Flows.
'Pressure vs. Pressure Guarantee
'Dim objCond As Spire.Xls.ConditionalFormatWrapper
'objCond = sheet.Range(2, 2, sheet.LastRow, 2).ConditionalFormats.AddCondition()
'objCond.FormatType = ConditionalFormatType.Formula
'objCond.FirstFormula = "=($B2<$L2)"
'objCond.BackColor = Color.LightCoral
'Flow vs. Flow Guarantee
'objCond = sheet.Range(2, 3, sheet.LastRow, 3).ConditionalFormats.AddCondition()
'objCond.FormatType = ConditionalFormatType.Formula
'objCond.FirstFormula = "=($C2>$N2)"
'objCond.BackColor = Color.LightSkyBlue
'DewPoint vs. DewPoint Guarantee
'objCond = sheet.Range(2, 5, sheet.LastRow, 5).ConditionalFormats.AddCondition()
'objCond.FormatType = ConditionalFormatType.Formula
'objCond.FirstFormula = "=($E2>$M2)"
'objCond.BackColor = Color.PaleGreen
'Save Spreadsheet
workbook.SaveToFile(MyAppPath & "TextData\" & PdateNorm & " 15MinAvg.xlsx", ExcelVersion.Version2010)
Catch excptn As Exception
FrmLabelErrMess2.LabelErrMess.Text = Err.Description & " Workbook Error Pnt5, " & DateTime.Now.ToString()
Call EncrFileRout(CStr("MAFileBU\BackUpFrm\WorkbookUpdate Error Pnt5, " & Err.Description))
ContinueWithWorkbook = False
End Try
End If
End If
End If
End If
End If
End Using
'If ContinueWithWorkbook Then
' FrmLabelErrMess2.LabelErrMess.Text = "Workbook Update Complete, " & DateTime.Now.ToString()
'End If
Catch excptn As Exception
FrmLabelErrMess2.LabelErrMess.Text = Err.Description & " Workbook Error Pnt6, " & DateTime.Now.ToString()
Call EncrFileRout(CStr("MAFileBU\BackUpFrm\WorkbookUpdate Error Pnt6, " & Err.Description))
End Try
End If
Catch excptn As Exception
FrmLabelErrMess2.LabelErrMess.Text = Err.Description & " Workbook Error Pnt7, " & DateTime.Now.ToString()
Call EncrFileRout(CStr("MAFileBU\BackUpFrm\WorkbookUpdate Error Pnt7, " & Err.Description))
End Try
workbook = Nothing 'testing
End Sub
