VB 6.0 - vb6 to excel - Asked By Ramesh Dudha on 01-Apr-12 02:33 PM

sir, in my working disillusionment, one program opens Excel file and it updates "sheet1" worksheet. i just want to excess it through vb6. is that possible? if so please guide. thanks. Ramesh Dudha  
Abhinav Sejpal replied to Ramesh Dudha on 01-Apr-12 04:53 PM
Here is the code...

Private Sub cmdOpenExcel_Click()
On Error GoTo ErrHandler
    Dim xlsApp As Object
    Dim xlsWB1 As Object
    'Late binding to open an XLS file which is present on my local harddisk
    Set xlsApp = CreateObject("Excel.Application")
    xlsApp.Visible = True
    Set xlsWB1 = xlsApp.Workbooks.Open(strFileName)
    Exit Sub
    MsgBox "There is a problem while opening the xls document. " & _
    " Please ensure it is present!", vbCritical, "Error"
End Sub

Now, since I know that my Excel file (which I want to work with) has 15 columns and 200 rows, here is what I did to read all the content to an Array for further manipulation.

Private Sub cmdParse_Click()
On Error GoTo ErrHandler:
    Dim xlsApp As Object
    Dim xlsWB1 As Object
    Dim xlsWS1 As Object
    'Opening the file to parse now
    Set xlsApp = CreateObject("Excel.Application")
    xlsApp.Visible = False
    Set xlsWB1 = xlsApp.Workbooks.Open(strFileName)
    Set xlsWS1 = xlsWB1.Worksheets("Sheet1")
    Dim col As Integer
    Dim row As Integer
    Dim str As String
    str = ""
    MaxRow = 200
    MaxCol = 15
    'Declaring an array so that we don't have to depend on the excel file anymore
    ReDim CaseArray(MaxRow, MaxCol)
    'Reading the Excel file and putting everything in Memory for faster manipulation
    For row = 1 To MaxRow
      For col = 1 To MaxCol
        CaseArray(row, col) = xlsWS1.cells(row, col).Value
    Set xlsApp = Nothing
    Set xlsWB1 = Nothing
    Set xlsWS1 = Nothing
    Exit Sub   
    MsgBox "An unknown error occurred while Parsing the Excel. Sorry about that!!" , vbCritical, "Error"
End Sub

In my case, CaseArray was a 2 dimensional Array using which I used in the other modules to manipulate the data as per my requirements!

Hope that helps!
Anoop S replied to Ramesh Dudha on 02-Apr-12 06:53 AM
This is just a program that just adds some text into a cell on both sheet1 and sheet2.

Option Explicit
'Add a reference to MS Excel xx.0 Object Library
Private moApp As Excel.Application
Private moWB As Excel.Workbook
Private Sub Form_Load()
  Set moApp = New Excel.Application
  moApp.Visible = False
End Sub
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
  If TypeName(moWB) <> "Nothing" Then
    moWB.Close True, "C:\Text.xls"
  End If
  Set moWB = Nothing
  If TypeName(moApp) <> "Nothing" Then
  End If
  Set moApp = Nothing
End Sub
Private Sub Command1_Click()
  Set moWB = moApp.Workbooks.Add
  moApp.Visible = True
  moWB.Sheets("Sheet2").Cells(1, 1).Value = "Added from VB6"
  moWB.Sheets("Sheet1").Cells(1, 1).Value = moWB.Sheets("Sheet2").Cells(1, 1).Value
End Sub