[RESOLVED] VB6 OLE Object to Excel 365

[prinslft]

This problem is driving me nuts.

It is an attempt to help a friend of mine.

Using VB6, Ole and Excel 365

Two command buttons is used, ComEx and ComAcc. Also the ole object loading an Excel sheet
The first command "ComEx" creates the file and it can be opened in excel.
The second command ComAcc loads this created file into the object OLESL. It populates the
sheet and display the changes that is made, then saved to excel and this is where the
popo hits the fan. The excel sheet can not be opened in excel. I attach the code for
buttons.
Private Sub ComEx_Click()
Dim vRow, vCol As Integer


Path = Me.Dir1 & "\StockList " & Format(Now(), "MMM-YY")

OLESL.CreateEmbed vbNullString, "Excel.Sheet"
Set oBook = OLESL.object
Set oSheet = oBook.Sheets(1)

oSheet.Cells(1, 1).Value = "Stock List"
oSheet.Cells(1, 2).Value = Format(Now(), "MMM-YY")
oSheet.Cells(1, 4).Value = "Total KG"
oSheet.Cells(1, 5).Value = "Total Price"

oSheet.Cells(5, 1).Value = "FullChicken"
oSheet.Cells(5, 2).Value = "Price"
oSheet.Cells(5, 3).Value = "Fillet"
oSheet.Cells(5, 4).Value = "Price"
oSheet.Cells(5, 5).Value = "Thighs"
oSheet.Cells(5, 6).Value = "Price"
oSheet.Cells(5, 7).Value = "Drums"
oSheet.Cells(5, 8).Value = "Price"
oSheet.Cells(5, 9).Value = "Wings"
oSheet.Cells(5, 10).Value = "Price"
oSheet.Cells(5, 11).Value = "Starpack"
oSheet.Cells(5, 12).Value = "Price"
oSheet.Cells(5, 13).Value = "Liver"
oSheet.Cells(5, 14).Value = "Price"
oSheet.Cells(5, 15).Value = "Buffalo Wings"
oSheet.Cells(5, 16).Value = "Price"
oSheet.Cells(5, 17).Value = "Flatties"
oSheet.Cells(5, 18).Value = "Price"
oSheet.Cells(5, 19).Value = "Marinated Flatties"
oSheet.Cells(5, 20).Value = "Price"

' oSheet.ActiveCell.FormulaR1C1 = "=SUMIF(R[2]C:R[96]C,""> 0"")"
vRow = 4
vCol = 1
For vCol = vCol To 20
oSheet.Cells(vRow, vCol).Value = "=SUMIF(R[2]C:R[900]C,""> 0"")"
''oSheet.NumberFormat = "0.0000"
Next vCol
oSheet.Range("A4,C4,E4,G4,I4,K4,M4,O4,Q4,S4").NumberFormat = "0.0000"
'' oSheet.NumberFormat = "0.0000"
oSheet.Range("B4,D4,F4,H4,J4,L4,N4,P4,R4,T4").NumberFormat = "$ #,##0.00"
''oSheet.NumberFormat = "$ #,##0.00"
oSheet.Cells(2, 4).Value = "=Sum(A4, C4, E4, G4, I4, K4, M4, O4, Q4, S4)"
oSheet.Cells(2, 5).Value = "= Sum(B4, D4, F4, H4, J4, L4, N4, P4, R4, T4)"
''oSheet.cells(2, 4).Value = SUM(R[2]C[-3]),(R[2]C[-1]),(R[2]C[1]),(R[2]C[3]),(R[2]C[5]),(R[2]C[7]),(R[2]C[9]),(R[2]C[11]),(R[2]C[13]),(R[2]C[15])
'' oSheet.cells(2, 5).Value = =SUM(R[2]C[-3],R[2]C[-1],R[2]C[1],R[2]C[3],R[2]C[5],R[2]C[7],R[2]C[9],R[2]C[11],R[2]C[13],R[2]C[15])
oSheet.SaveAs Path & ".XLSX"

oBook.Close Savechanges:=True

Set oSheet = Nothing
Set oBook = Nothing
Me.ComAcc.Enabled = False
Me.ComEx.Enabled = False
J = MsgBox("Stocklist for " & Format(Now(), "MMM-YY") & " has been created and saved at " & Path & " ", vbOK, "Stocklist Created")
End Sub

This code work

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Sub ComAcc_Click()
If Combo1.Text = "" Or Me.TxtW.Text = "" Then Exit Sub
Dim F As Integer


If Path = "" Then
J = MsgBox("File path not set, Program is going to exit", vbOKOnly, "Path Empty")
Exit Sub
End If
If InStr(1, Path, ".") Then
Path1 = Path
Else
F = F + 1
Path1 = Path & ".XLSX"
End If
OLESL.CreateEmbed Path1
Set oBook = OLESL.object
Set oSheet = oBook.Sheets(1)


''Search first blank in correct row
Call Soek
M = oSheet.Cells(Row1, colum).Value


'' oSheet.Cells(Row1, colum)
If Row1 = "" Then Exit Sub
weight = Me.TxtW.Text
price = Me.Text1.Text

M = CStr(oSheet.Cells(Row1, colum).Value)
desc = (oSheet.Cells(5, colum).Value)
If desc = "" Then Exit Sub

Do
M = CStr(oSheet.Cells(Row1, colum).Value)
If M = "" Then
oSheet.Cells(Row1, colum).Value = weight
oSheet.Cells(Row1, colum + 1).Value = price


''
Exit Do
End If
Row1 = Row1 + 1
Loop Until M = ""




Me.ComAcc.Enabled = False
oSheet.SaveAs Path & F & ".XLS"
oSheet.SaveAs Path & F & ".csv"
oSheet.SaveAs Path & F & ".XLSX"
'' oSheet.SaveAs Path & F & ".XLSM"
oSheet.SaveAs Path1
oBook.Close Savechanges:=True

Set oSheet = Nothing
Set oBook = Nothing
'' OLESL.Close
'' OLESL.Delete

End Sub
This code does not allow me to open sheet in Excel.

[prinslft]

The Ole object it creates, and after saving it to Excel it displays the attached file.

Any takes on this??

[prinslft]

Thanks, I have found the problem.
adding one line of code

OLESL.Object.SaveAs (Path)