VB6 EXCEL PROBLEM

Zam Zam · August 5th, 2010, 09:21 PM

Hi,

I have a excel file with header on the first row and large amount of rows. what i want i to split the number of rows in the first excel workbook evenly into multiple new workbook with the header included. the format of the new workbook must be the same as the original data

Could anyone show me how to do it using vb6

Shasur · August 5th, 2010, 10:19 PM

Hi

Coding with VB6 is similar to VBA. You need to add a Reference to Microsoft Excel Object library and use the Excel objects.

You can try the code in Excel's VB editor and use it in VB6.0

Cheers
Shasur

Zam Zam · August 10th, 2010, 08:16 PM

Hi Shasur,

Could you guide or show me how to start?

Thank you

Shasur · August 11th, 2010, 09:44 PM

Quote:

Originally Posted by Zam Zam

Hi Shasur,

Could you guide or show me how to start?

Thank you

First add a reference to Microsoft Excel library in VB6.0. You can do that by Projects-->References-->Browse from Microsoft Excel and enable the check box

Once you add this all the Excel objects are available for your project

Now you need to Open and Excel instance. You can do that by

Dim oXL - Excel.Application
Set oXL = New Excel,Application

this opens a new Excel. You can make that visible by

oXl.Visible = True

and then you can open the workbook

Dim oWB as Excel.Workbook
Set oWB = oXL.Workbooks.Open (Path and Name of Workbook here)

now you can get the sheets

Dim oWS as worksheet
Set oWS = oWB.Worksheets(1)

from here you can add the logic to the worksheet .

once you are done. you need to close all you have opened

oWB.Save 'If you want to save the workbook
oWB.Close

oXL.Quit ' will close Excel

and set the objects to nothing
oWB =nothing
oXL = nothing

Follow each step and take your own time. Happy coding

Cheers
Shasur

Zam Zam · August 16th, 2010, 08:39 PM

Hi Shasur,

I can read through teh main excel sheets/workbook.
but what i want is is copy the exect same data without formating the numeric value from the main workbook to several new workbook evenly.

how could i do that?

regards

Shasur · August 16th, 2010, 11:11 PM

Hi

Do you want to copy the entire sheet to a new workbook or some contents of the sheet to a new workbook

Cheers
Shasur

Zam Zam · August 16th, 2010, 11:21 PM

Hi Shasur,

My scenario is like this
i have an excel sheet eg. a.xls and in a.xls there are 501 rows of data. where first row is the header for each column

what i want is to split the number of rows to b.xls (first 250 rows from a.xls) and c.xls (next 250 rows from a.xls) with the header included from a.xls

meaning i want the data and format copy into new workbook remain unchanged and also the original data a.xls remain unchanged

the rows and columns of data in the a.xls will be different every time where i just need to split the main excel sheet evenly into 2 new workbook with the header included and data remain unchanged

kindly show me how to do that

regards

Shasur · August 17th, 2010, 09:32 PM

Hi

Here is a hint:

Code:

Sub Split_Current_Sheet()
Dim oWS As Worksheet
Dim oWB As Workbook
Dim iMaxRow As Long
Dim iHalfRow As Long
Dim oCopy As Worksheet
Set oWS = ActiveSheet
iMaxRow = oWS.Cells.SpecialCells(xlCellTypeLastCell).Row
iHalfRow = iMaxRow / 2
Set oWB = Workbooks.Add
Set oCopy = oWB.Sheets(1)
oWS.Rows(1).EntireRow.Copy oCopy.Range("A1")
oWS.Range("A2", "Z" & CStr(iHalfRow)).EntireRow.Copy oCopy.Range("A2")
oWB.SaveAs "c:\b.xls"
oWB.Close False
Set oWB = Workbooks.Add
Set oCopy = oWB.Sheets(1)
oWS.Rows(1).EntireRow.Copy oCopy.Range("A1")
oWS.Range("A" & CStr(iHalfRow + 1), "Z" & CStr(iMaxRow)).EntireRow.Copy oCopy.Range("A2")
oWB.SaveAs "c:\c.xls"
oWB.Close False
End Sub

Cheers
Shasur

Zam Zam · August 17th, 2010, 09:44 PM

Hi Shasur,

i got the error "Copy Method of Range Class Failed"
any advise on the error?

regards?

Shasur · August 18th, 2010, 03:59 AM

Hi

Can you post the line where the error comes. There might be a possibility that the Range is not assigned properly

Cheers
Shasur