Library code snippets
Working with Excel Files Using VB6
By Adil Hussain Raza, published on 01 Feb 2006
The code is totally self explanatory, In the load event we're going to open the new instance of the excel library and our excel file “book1.xls” will be accessible from our code. Then we'll use Command1 to retrieve data from book1, please note that you must have some data in the excel file. Similarly Command2 is used to put/replace the data in the excel sheet cells.
'do declare these variables you need to add a reference
'to the microsoft excel 'xx' object library.
'you need two text boxes and two command buttons
'on the form, an excel file in c:\book1.xls
Dim xl As New Excel.Application
Dim xlsheet As Excel.Worksheet
Dim xlwbook As Excel.Workbook
Private Sub Command1_Click()
'the benifit of placing numbers in (row, col) is that you
'can loop through different directions if required. I could
'have used column names like "A1" 'etc.
Text1.Text = xlsheet.Cells(2, 1) ' row 2 col 1
Text2.Text = xlsheet.Cells(2, 2) ' row 2 col 2
'don't forget to do this or you'll not be able to open
'book1.xls again, untill you restart you pc.
xl.ActiveWorkbook.Close False, "c:\book1.xls"
xl.Quit
End Sub
Private Sub Command2_Click()
xlsheet.Cells(2, 1) = Text1.Text
xlsheet.Cells(2, 2) = Text2.Text
xlwbook.Save
'don't forget to do this or you'll not be able to open
'book1.xls again, untill you restart you pc.
xl.ActiveWorkbook.Close False, "c:\book1.xls"
xl.Quit
End Sub
Private Sub Form_Load()
Set xlwbook = xl.Workbooks.Open("c:\book1.xls")
Set xlsheet = xlwbook.Sheets.Item(1)
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set xlwbook = Nothing
Set xl = Nothing
End Sub
Adil is doing MCS from Virtual University Of Pakistan. He is working as a software developer for 3 years. He has developed several customized commercial software in Haroon Abad City for various cli...
Related articles
Related discussion
-
VB6 system conversion using VBA to Word 2007
by b.macgregor@vodamail.co.za (0 replies)
-
How to open .bat application from excel VBA or VB6
by NaseemAhmed (0 replies)
-
Outlook VBA query
by James Crowley (1 replies)
-
How to control IE from VBA
by NaseemAhmed (0 replies)
-
Run-time error '91'
by converter2009 (1 replies)
Related podcasts
-
Christian Beauclair
14 mai 2008 (�mission #0074) ::.Christian Beauclair: Stratégies de migration VB6 vers .NET Nous discutons avec Christian Beauclair des stratégies de migration VB6 vers .NET. Entre autres, nous discutons comment utiliser le "VB 6 Code Advisor" et le "Interop Forms Toolkit" pour ajouter la puiss...
can i ask... how to unmerge the sheet on the excel sheet using vb?
Vrom VB6 How would you execute a macro that was embedded in this workbook?
1. Click menu "Project"
2. Select "Reference..."
3. Check the item "microsoft excel 'xx' object library"
I wonder if the problem is related to the lines:
in Command1_Click() and Command2_Click(). When I move these two lines to Form_Unload(), before the set nothing statement, the problem solved.
Could somebody please help me on how to add a reference 'to the microsoft excel 'xx' object library. I read your code and seems nice but am a newbie in vb and i don't know how add reference to microsoft excell. I seriosly want to be able to send the data from a datagrid to an excel sheet for anaylsis. Please help.
Hi, I tried your code with 2 command buttons and two text boxes. There is no syntax error. However at run time ONLY 1 command button works at a time. Clicking the 2nd button causes an error. An error is highlighted in debug for the 2nd button, even though there is no error.Closing the program and re-running it , this time clicking on the 2nd command button first and the 1st one second, gives an error message and shows a line that is error free!. What should I do
Pls help Regds Balachandran<head><title>Customers</title></head>
<body background="../image/niceblue.gif">
<TABLE cellpadding="5" border="3">
<tr><td>
<h1>Customers</h1>
ECG has approximately 1.4 million customers made up of 120 thousand <br>
Prepaid customers and 1.28 million Credit Customers as at end of May,2006.
<br>
Customers who had been supplied with any of the types of <a href = "meters.html">prepaid meters</a>
are<br> known as prepaid customers.
Customers using credit meters are known as credit<br>
customers,
simply because the former makes payment before power is consumed<br>
where as the latter consumes power before payment is made.<br>
<hr>
<br>
<h2>Types of Customers</h2>
<br>
ECG customers are categorised into 2 broad ones:<BR><BR>
1. Special Load Tarrif -(SLT Customers)
Special Load Tariff (SLT) is paid by <br>customers whose load requirement<br> is 200 KVA and above.
<br><br>
An SLT customers can be one of the following types:
<ul>
<Li> High Voltage - HV</Li>
<Li> Medium Voltage - MV</Li>
<Li> Low Voltage - LV</Li>
</ul>
<hr>
2. Non Special Load Tarrif -NSLT Customers<br>
Non Special Load Tariff (Non SLT) are paid by customers whose load requirements<br> is below 200 KVA.
<br>
SLT & Non SLT receipts form the bulk of the company’s revenue.
<br><BR>
The NSLT Customers are of 2 types:
<ul>
<Li> Residential - HV</Li>
<Li> Non Residential - MV</Li>
</ul>
Non Special Load Tariff (Non SLT) are paid by customers whose load requirements<br>
is below 200 KVA. SLT & Non SLT receipts form the bulk of the company’s revenu
</td>
<td><h3><font color ="red">Read More about:</font></h3><br>
<img src ="../image/nobulls1.gif" width="20" Height="20"><a href="cus.html" target="show"> Customer Population</a><br>
<BR>
<img src ="../image/nobulls1.gif" width="20" Height="20"><a href="servcon.htm" target="show">Getting New Service Connection</a><br>
<BR>
<img src ="../image/nobulls1.gif" width="20" Height="20"><a href="elecuse.htm" target="show">Using Electricity efficiently</a><br><br>
<BR>
<img src="../image/subst.jpg" height="550" width="500" Alt="ECG Customer operating a Prepaid meter"><br>
<i> a Substation ar Achimota
</td>
</tr>
</table>
</body>
If I wanted to read into VB6 from excel, lets say 100 rows (containg 8 or 9 cells of numerical data, e.g. A1, B1, C1...,) and i wanted to use each row's data to do certain calculations and after each row is processed I want to go to the next row. How would write the code to go from one row in excel to the next row automatically. And how would my program know when it reached the end of file (excel file)?
If you could keep the code as simple and clear as the code you have above that would be great.
Thanks,
Donnovan
hey, i cannot get the code snippet to work i guess that i need to import a namespace or something??
It is work, but there's something I want to ask you more. Can you tell me how to set coloumn/cell fillcolor with any condition? Thx
Regards
Ocky
Hi,
Thnx for this piece of code, as this was very usefule for my application development.. Thnx a lot dude
Harish
Chennai, India
Hello,
Would u know how to compare .XLS or .CSV then output the results to a txt file?
This thread is for discussions of Working with Excel Files Using VB6.