Editing An Excel Spreadsheet From VB

[softweng]

I need to edit the contents of various Excel spreadsheets from VB. The machines will not have
Excel on them so I can't shell it out. I have tried using an ADODC control and populating a
DataGrid. It populates just fine but when you make a change and the data control updates the
spreadsheet you cannot open it anymore. Also if there are two or more records that are identical
the data control cannot handle it and errors out.
Is there any other way to do this?

Kris
Software Engineer
Phoenix,AZ

[vchapran]

Have you tried to work with Excel Object Library (Automation). Just steps:
In your VB project add reference for Excel, create Excel object (book, spreadsheet, whatever) and work with its properties and methods.
Vlad

[softweng]

Yes I use the Excel Object Library. But I need to show the data to the user, allow them to change
it, and save it back to the spreadsheet. I could
do this cell by cell but that is to slow.

Kris
Software Engineer
Phoenix,AZ

[vchapran]

If it's slow because of the speed of the user, you cannot do anything, if you change data without user intervention (from database or some other data source), I do not understand your problem.
Sorry. Some additional details could help.
Vlad

[Iouri]

If it is not a big spreadsheet try this code


'Write Excel files directly without using Excel

'form
private Sub Command1_Click()
Dim colu as Byte
Dim rw as Byte

'create new excel class called ef1
Dim ef1 as new ExcelFile

With ef1
'open it path will be c:\vbtest.xls
.OpenFile "vbtest.xls"
'write integer data @ col 1, row 1
.EWriteInteger 1, 1, 100
'write a string @ col 2, row 1
.EWriteString 1, 2, "Test writing a string"
'write another string @ col 3, row 1
.EWriteString 1, 3, "gerry"
.CloseFile
End With

End Sub


'class ExcelFile


'Beginning Of File record
private Type BOF
opcode1 as Byte
opcode2 as Byte
length1 as Byte
length2 as Byte
version1 as Byte
version2 as Byte
ftype1 as Byte
ftype2 as Byte
End Type

'End Of File record
private Type EOF
opcode1 as Byte
opcode2 as Byte
length1 as Byte
length2 as Byte
End Type

'Integer record
private Type tInteger
opcode1 as Byte
opcode2 as Byte
length1 as Byte
length2 as Byte
row1 as Byte
row2 as Byte
col1 as Byte
col2 as Byte
rgbattr1 as Byte
rgbAttr2 as Byte
rgbAttr3 as Byte
w1 as Byte
w2 as Byte
End Type

'Label (Text) record
private Type tLabel
opcode1 as Byte
opcode2 as Byte
length1 as Byte
length2 as Byte
row1 as Byte
row2 as Byte
col1 as Byte
col2 as Byte
rgbattr1 as Byte
rgbAttr2 as Byte
rgbAttr3 as Byte
length as Byte
End Type


Dim fhFile as Integer
Dim bof1 as BOF
Dim eof1 as EOF
Dim l1 as tLabel
Dim i1 as tInteger

public Sub OpenFile(byval FileName as string)
fhFile = FreeFile
Open FileName for binary as #fhFile
Put #fhFile, , bof1

End Sub

public Sub CloseFile()

Put #fhFile, , eof1
Close #fhFile

End Sub


private Sub Class_Initialize()
'set up default values for records
'These should be the values that are the same for every record

With bof1
.opcode1 = 9
.opcode2 = 0
.length1 = 4
.length2 = 0
.version1 = 2
.version2 = 0
.ftype1 = 10
.ftype2 = 0
End With

With eof1
.opcode1 = 10
End With

With l1
.opcode1 = 4
.opcode2 = 0
.length1 = 10
.length2 = 0
.row2 = 0
.col2 = 0
.rgbattr1 = 0
.rgbattr1 = 0
.rgbattr1 = 0
.length = 2
End With

With i1
.opcode1 = 2
.opcode2 = 0
.length1 = 9
.length2 = 0
.row1 = 0
.row2 = 0
.col1 = 0
.col2 = 0
.rgbattr1 = 0
.rgbAttr2 = 0
.rgbAttr3 = 0
.w1 = 0
.w2 = 0
End With

End Sub

Function EWriteString(r as Byte, c as Byte, t as string)
Dim b as Byte
Dim l as Byte
stringtowrite = t
l = len(stringtowrite)

'Length of the text portion of the record
l1.length = l

'Total length of the record
l1.length1 = 8 + l

'BIFF counts from zero
l1.row1 = r - 1
l1.col1 = c - 1

'Put record header
Put #fhFile, , l1
'then the actual string data
for a = 1 to l
b = Asc(mid$(stringtowrite, a, 1))
Put #fhFile, , b
next

End Function

Function EWriteInteger(r as Byte, c as Byte, i as Long)

With i1
.row1 = r - 1
.col1 = c - 1
.w1 = i - (Int(i / 256) * 256)
.w2 = Int(i / 256)
End With

Put #fhFile, , i1

End Function




Iouri Boutchkine
[email protected]

[Ghost308]

I'm not sure I'm understanding the problem completely but it sounds like you want the user to have full control of the data from the spreadsheet in your app? Try placing an OLE object on your form and link it to the xls file. Make sure you set the ole object to display the file and not just an icon representing the file. Hope that helps some!

[softweng]

what i need to do is show the contents of the spreadsheet to the user with a grid or something.
then allow them to change whatever data they want and then save the changes to the spreadsheet.
I have tried using a DataGrid and ADODC control for this. It loads the data into the grid just fine
but when the user makes changes and the
data control updates the spreadsheet, the spreadsheet can longer be opened.

Kris
Software Engineer
Phoenix,AZ

[vchapran]

First of all, it's bad idea to use Data Control. Use ADODB library. I'm not sure, that anything else, except displaying of data can be done with DC. For everything else you have some kind of problem with that guy (DC).
Populate Grid with data from ADO Recordset, make changes and then depending on if you want to keep those changes in your data source either directly display it in SpreadSheet using loop to read data from Grid and Spreadsheet methods and properies, or save data before and then populate spreadsheet from recordset. Unfortunately I do not have ready to use sample, but it's easy.
Vlad

[Share2]

Use EasyGrid Control, it can open excel files directly.
You may goto http://www.share2.com/easygrid/ to download a trial version and try it.