?
Solved

Working with excel files from VB

Posted on 2003-02-28
11
Medium Priority
?
376 Views
Last Modified: 2010-08-05
Hi,
I want to connect my excel files with visual basic program and to use those files from the visual basic window such that I can perform the  excel operations as like excel files.

With thanks.

Shakhawat
0
Comment
Question by:Shakhawat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 7

Accepted Solution

by:
fluglash earned 200 total points
ID: 8042989
OLE container
0
 

Author Comment

by:Shakhawat
ID: 8043773
I want to open the file command. Can anybody guide me in that direction.
0
 

Author Comment

by:Shakhawat
ID: 8043910
I want to open the file using command button. Can anybody suggest me.
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:Shakhawat
ID: 8043935
I tried with that but I could not execute it. I want to open the file from command button from VB application and use the form as of excel form.
0
 
LVL 1

Expert Comment

by:spiritwithin
ID: 8045750
Alright. It's not that hard. Do it like this:

In Visual Basic, select Project -> Referers (or however it is called in english, i got a german version. Anyway, choose the item above "Components" if you do not know what i mean).

In there, if MS Excel is installed on your system, you should be able to select "Microsoft Excel 9.0 Object Library".

Then add a module and add the following code:

Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet

Public Sub loadExcelFile(ByVal strFileName As String)

    Set objExcel = GetObject(, "Excel.Application")
    If Err.Number Then
       Err.Clear
       Set objExcel = CreateObject("Excel.Application")
       If Err.Number Then
          MsgBox "Can't open Excel."
       End If
    End If
    objExcel.Visible = True
   
    Set objWorkbook = objExcel.Workbooks.Open(strFileName)
    Set objWorksheet = objWorkbook.ActiveSheet

End Sub

Go to your form, and add this to the Form_Unload Event:

Public Sub Form_Unload()

    ' This resets the pointers to nothing, because
    ' we are done (since we are unloading the form)

    Set objExcel = Nothing
    Set objWorkbook = Nothing
    Set objWorksheet = Nothing

End Sub

You then add a Textbox (txtFilename) and a command button (cmdLoad) to your form.

In your form code goto:

Public Sub cmdLoad_Click()

    loadExcelFile txtFilename.Text

End Sub


That's it. Run the thing, enter the filename of an Excel file with full path into txtFilename and click the cmdLoad button. Visually nothing will happen, except that MS Excel opens up, but internally the Excel file will load and you will be able to access the current worksheet's data through objWorksheet.

An example for reading data from the excel sheet, let's say read the text from cell 2 in row 5 would be.

Dim strText as String

strText = objWorksheet.Cells(5, 2)

' strText now contains the content of row 5, cell 2.


I had to mess with the same question for a client's application, not long ago. I searched and also asked here on experts-exchange, and i came to the conclusion that using the above method is the easiest, and best. Though it assumes that MS Excel is installed on the system where the program is being used.

Note the two above functions, they will be helpful for you when you work with the Excel sheet. Thanks to forgot_your_name for those functions:


Function getLastColumn(ws As Worksheet) As Single

 On Error Resume Next
 
 With ws
     getLastColumn = .Cells.Find(What:="*", _
     SearchDirection:=xlPrevious, _
     SearchOrder:=xlByColumns).Column
 End With

End Function

Function getLastRow(ws As Worksheet) As Single
 
 On Error Resume Next

 With ws
   getLastRow = .Cells.Find(What:="*", _
     SearchDirection:=xlPrevious, _
     SearchOrder:=xlByRows).Row

 End With

End Function
0
 

Author Comment

by:Shakhawat
ID: 8052427
Hi,
I tried with the code several times. It does not work. Can anybody suggest me.

Thanks in advance.
0
 

Author Comment

by:Shakhawat
ID: 8052906
Hello,

I tried with the above codeand process. It is not working. Can anybodytell me details. I want to open my excel sheet from visual basic userform. I am not getting it.

Thanks in advance

Shakhawat
0
 
LVL 1

Expert Comment

by:spiritwithin
ID: 8053366
I am using exactly the above method, successfully to provide a client's application with xls support.
0
 
LVL 1

Expert Comment

by:spiritwithin
ID: 8053395
Maybe you can make use of this here, it saves a flexgrid's content as an excel file. Imagine reversing the whole process, you would have a useable loading function... maybe it is worth a try.

Public Function FG_SaveAsExcel(FG As MSFlexGrid, sFileName As String, Optional sHeader As String = "", Optional sFooter As String = "" )
  Dim myExcel As ExcelFileV2
  Dim lRow As Integer, lCol As Integer
  Dim excelDouble As Double
  Dim rowOffset As Long
  Dim aTemp() As String
 
  If Len(sHeader) > 0 Then
    aTemp = Split(sHeader, vbTab)
    rowOffset = UBound(aTemp) + 1
  End If
 
  Set myExcel = New ExcelFileV2
 
  With myExcel
 
    .OpenFile sFileName
   
    ' Heading
    For lRow = 1 To rowOffset
      .eWritestring lRow, 1, aTemp(lRow - 1)
    Next lRow

    ' FlexGrid -> Fixedrows
    For lRow = 1 To FG.FixedRows
      For lCol = 1 To FG.Cols
        .eWritestring lRow + rowOffset, lCol, FG.textmatrix(lRow - 1, lCol - 1)
      Next lCol
    Next lRow
 
    ' Data
    For lRow = FG.FixedRows + 1 To FG.Rows
     
      ' FlexGrid -> Fixedcols
      For lCol = 1 To FG.FixedCols
        .eWritestring lRow + rowOffset, lCol, FG.textmatrix(lRow - 1, lCol - 1)
      Next lCol
     
      ' FlexGrid -> Data
      For lCol = FG.FixedCols + 1 To FG.Cols
        If IsNumeric(FG.textmatrix(lRow - 1, lCol - 1)) Then
          excelDouble = CDbl(FG.textmatrix(lRow - 1, lCol - 1)) + 0
          .EWriteDouble lRow + rowOffset, lCol, excelDouble
        Else
          .eWritestring lRow + rowOffset, lCol, FG.textmatrix(lRow - 1, lCol - 1)
        End If
      Next lCol
    Next lRow
   
    ' Footer
    If Len(sFooter) > 0 Then
      aTemp = Split(sFooter, vbTab)
      For lRow = 0 To UBound(aTemp)
        .eWritestring lRow + rowOffset + FG.Rows + 1, 1, aTemp(lRow)
      Next lRow
    End If
   
    .CloseFile
  End With
 
End Function
0
 

Author Comment

by:Shakhawat
ID: 8810502
Hi,
 I need to connect Matlab from visual basic 6. Can you help me?

Shakhawat
0
 

Expert Comment

by:CleanupPing
ID: 9447436
Shakhawat:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Six Sigma Control Plans
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question