VB6 How to read and write to an Excel file

[jamesg]

How can Vb6 read from an Excel file and store the data in an Array with two dimensions. The data from cell A1 to store in Arrays a11 , the cell B1 to store in a12 the A2 to store in a21 the
cell B2 to store in a22 etc.
Also how can write to an Excel file from An Array With two dimensions and the data to go to the excel file as above
a11 to cell A1 , a12 to cell B1, a21 to cell A2, a22 to cell B2 etc

[opus]

I suppose your Array is more like a(1,1), a(1,2) ....
The code to read from file

Dim xlApp As Excel.Application 
Dim xlWB As Excel.Workbook 
Dim xlSH As Excel.Worksheet 
Dim i As Integer
Dim j As Integer
'open excel application 
Set xlApp = New Excel.Application 
'Open excel workbook 
Set xlWB = xlApp.Workbooks.Open(FileName:="C:\YourFile.xls" ) 
'Suppose you read from Sheet 1
Set xlSH = xlWB.Worksheets(1) 
Reads Cells A1 to E10 into a(1,1) to a(10,5)
For i=1 to 5
   for j=1 to 5
        a(i,j)=xlSh.Cells(i,j).Value
   Next j
Next i
'Close workbook (optional) 
xlWB.Close 
'Quit excel (automatically closes all workbooks) 
xlApp.Quit 
'Clean up memory (you must do this) 
Set xlWB = Nothing 
Set xlApp = Nothing

The code to write into the file

Dim xlApp As Excel.Application 
Dim xlWB As Excel.Workbook 
Dim xlSH As Excel.Worksheet 
Dim i As Integer
Dim j As Integer
'open excel application 
Set xlApp = New Excel.Application 
'Open excel workbook 
Set xlWB = xlApp.Workbooks.Open(FileName:="C:\YourFile.xls" ) 
'Suppose you read from Sheet 1
Set xlSH = xlWB.Worksheets(1) 
Writes a(1,1) to a(10,5) into Cells A1 to E10 
For i=1 to 5
   for j=1 to 5
        xlSh.Cells(i,j).Value=a(i,j)
   Next j
Next i
'Close workbook (optional) 
xlWB.Close 
'Quit excel (automatically closes all workbooks) 
xlApp.Quit 
'Clean up memory (you must do this) 
Set xlWB = Nothing 
Set xlApp = Nothing

__________________

[jamesg]

Unfortunately didnot work
Reads Cells A1 to E10 into a(1,1) to a(10,5)
Writes a(1,1) to a(10,5) into Cells A1 to E10
Both give "Expected: end of Statement"
And when I had tried to run the program
Dim xlApp As Excel.Application gave "User -defined type not defined.

[JCScoobyRS]

Before you can use/reference Excel in VB, you have to have your project reference the Excel/Office Library. That's the problem. Late, Jeremy

[opus]

Oh, sorry, I forget to put the use the coment marks for the Read and Write line, also forget the reference to Excel.
That happens if you try to help, when your not on your programming computer! sorry

[jamesg]

I Still have problem to run the application correctly.
opus or JCScoobyRS please complete the application with the
reference to Excel.
Thanks

[JCScoobyRS]

Here is a simple app to open an Excel File and write to it then display it:

[reminder]Don't forget to reference the Excel Object Library or the Office Object Library[/reminder]

Private Sub Form_Load()
     Dim objExcel As Excel.Application
 
     Set objExcel = New Excel.Application
 
     objExcel.Workbooks.Open App.Path & "\Sample.xls" 'Create a Sample.xls file in the directory where your program is at.
     objExcel.Worksheets("Sheet1").Activate 'Activate the proper Workbook Sheet1
 
     objExcel.Cells(1,1).Value = "Hello!" 'The first 1 is for Row Number and the last is for Column Number
     objExcel.Cells(2,1).Value = "1"
     objExcel.Cells(3,1).Value = "2"
     objExcel.Cells(4,1).Value = "3"
     objExcel.Visible = True
End Sub

For more advanced techniques, search or run a macro and see how VBA does it then alter the code to work for VB. Let me know if this works, Jeremy

[JCScoobyRS]

For this to work you have to have these:

1) Excel/Office Library Referenced
2) A project saved to harddrive so App.Path can work
3) Sample.xls created and saved in same folder as project

That's it. Let us know, Jeremy

[jamesg]

What I dont understand is the reference to the Excel Object Library .How to add the reference to Excel/office Object Library

[JCScoobyRS]

When you have a project, you can follow these directions to reference Excel/Office Libraries:

1) While inside the project, on the top menu, click Project and go to the bottom where it says References... and click that.
2) Once in the references, scroll down until you get to:
Microsoft Office X.X Object Libray and put a check in the box
beside it. Note: X.X is version number like 10.0 or 9.0

That is all you have to do to reference Excel/Office. If you need more help, let me know. HTH, Jeremy

[opus]

When in the IDE, open the menu Project and References (i hope it'S the correct english name for that, it'as the third from the bottom). In the opening window you can select the checkmark next to the MS EXCEL Object libary.
That's it!
Dammed too slow!

[jamesg]

opus and JCScoobyRS Thank you very much .Now the program is working perfectly. During writing in the output Excel file if i want to
save changes with out asking me if i want to save changes what I have to add in the program?

[opus]

It should be something like

xlWb.Save

Assuning xlWb is an EXCEL-Workbook Object at that time!

[JCScoobyRS]

You also have to have this so the pop up doesn't come up:

objExcel.DisplayAlerts = False

That line lets you save, close, exit without the "Yes - No - Cancel" dialog box popping up. Later, Jeremy

[espua]

All,

I tried to use the same way to Export some data from VB apps to Excel file, but I encounter one weird problem. That's is, you must exit your VB application first (or unload the form) before you can open up the Excel file just exported from your applications.

anybody knows the reason behind this? and what is the work around for this??

[JCScoobyRS]

Not true. You have to do this:

objExcel.Visible = True

That will make the Excel.Application Object visible. Give that a try and let me know. Thanks, Jeremy

[espua]

Hi Jeremy,

I tried to use that objExcel.Visible = True. The outcome is the same. When I lauched the Excel while my Vb apps is still on, the Excel is running fine, but the "Workbook" sub-form just stuck there, I can not see the workbook exported from the VB apps. Both objExcel.Visible = True or lauching applications manually give the same result. I tried to run it on other PC, the result still the same.

I herewith attached the file for your reference. Apparently, this sample VB apps (i download it some where) unload the form to make it works.

[JCScoobyRS]

From looking at your project...I'd create a template Excel file and open it in code instead of creating a new one. It's easier to deal with and manage. I have posted code earlier in this thread with instructions and code. If you'd like more help, let me know. Thanks, Jeremy

[JCScoobyRS]

Just so you know though...I just ran your program and it created the Excel file and showed it to me. Kind of wierd. So when you click all the buttons in your program, the Excel object isn't visible or the worksheet isn't visible or what? HTH, Jeremy