Write to excel, VBA/VB6

[zedman3d]

Private Sub cmdCheck1_Click()

'Add a reference to MS Excel xx.0 Object Library
Dim oApp As Excel.Application
Dim oWB As Excel.Workbook
Dim oXLSheet As Excel.Worksheet
'Create an Excel instalce.
Set oApp = New Excel.Application
'Open the desired workbook
Set oWB = oApp.Workbooks.Open(FileName:="G:\Book1.xls")
'Open specifc sheet
Set oXLSheet = oWB.Worksheets(1)
'Do any modifications to the workbook.
'...
oXLSheet.Cells(2, 1).Value = "hello"
'Save the xls file
oWB.SaveAs FileName:="G:\Book1_MOD.xls"
'close and clean up resources
oWB.Close SaveChanges:=False
Set oWB = Nothing
oApp.Quit
Set oApp = Nothing
End Sub

What have i done wrong? Nothing happens at all.
Thank you.

 

[kmote]

Do you not get any errors either? Do you mean that G:\Book1_MOD.xls is empty or do you mean that it does not exist?

 

[filipk]

Hi,

i belive this is your problem:
Set oXLSheet = oWB.Worksheets(1)

you selected (1) sheet which is 2nd sheet in your workbook. Try to see if text you expected to be written in your Excel file is on 2nd sheet. If that is the problem, to write on first sheet use oWB.Worksheets(0). This is custom mistake when working with arrays, lists, etc.

If you want to improve your appliaction I advice you to use GemBox Excel component for .NET instead of Excel Automation for many reasons. Here you can see few of them why it's better to avoid Excel Automation.

Filip

 

[zedman3d]

Nope, none of the above.
Turns out i needed to include a reference to 12.0 Excel.

Thanks though.
PS: Its just for school so im not fussed about needing liscenses and such.