How To select all used rows and columns and display as text
How To select all used rows and columns and display as text
How To select all used rows and columns and display as text
How To select all used rows and columns and display as text
How To select all used rows and columns and display as text
How To select all used rows and columns and display as text How To select all used rows and columns and display as text How To select all used rows and columns and display as text How To select all used rows and columns and display as text How To select all used rows and columns and display as text How To select all used rows and columns and display as text How To select all used rows and columns and display as text How To select all used rows and columns and display as text
How To select all used rows and columns and display as text How To select all used rows and columns and display as text
How To select all used rows and columns and display as text
Go Back  Xtreme Visual Basic Talk > > > How To select all used rows and columns and display as text


Reply
 
Thread Tools Display Modes
  #1  
Old 11-24-2011, 06:02 PM
new_to_vb new_to_vb is offline
Newcomer
 
Join Date: Nov 2011
Posts: 2
Default How To select all used rows and columns and display as text


hi guys i have a question to all i have this function that i created below, this function gets all values in each cell of excel, but i do have a problem with this because its taking minutes to display the text from each cell, is there a way that i could make a function or call a excel method or property to get all values in a worksheet and put it in a string so i could display it or store it in a single column in our database.. actually our main purpose is to store all data that we could get into the database for reference and search criteria...
thak you for any help that you could give... so far this code works fro me but it take too long specially if my excel file contains multiple sheets...

Code:
 'strng the physical location of the excel file
Public Function processXLS(ByVal strng As String) As String
        On Error GoTo errhandler



        Dim xlsapp As Microsoft.Office.Interop.Excel.Application
        Dim xlsBook As Microsoft.Office.Interop.Excel.Workbook
        xlsapp = New Microsoft.Office.Interop.Excel.Application
        xlsapp.DisplayAlerts = False
        xlsBook = xlsapp.Workbooks.Open(Filename:=strng, UpdateLinks:=False, ReadOnly:=False)
        Dim xlworksheet As Microsoft.Office.Interop.Excel.Worksheet
        xlworksheet = New Microsoft.Office.Interop.Excel.Worksheet
        Dim str As New StringBuilder
        Dim str2 As StringBuilder
        str2 = New StringBuilder
        Dim irow As Integer
        Dim jcol As Integer
        Dim wsheetcount As Integer
        wsheetcount = xlsBook.Worksheets.Count
        For i As Integer = 1 To wsheetcount
            xlworksheet = xlsBook.Worksheets(i)
            xlworksheet.Activate()
            'Dim rowcount As Integer = xlsapp.ActiveSheet.UsedRange.Rows.Count()
            ' Dim colcount As Integer = xlsapp.ActiveSheet.usedRange.Columns.count
            For irow = 1 To 100
                For jcol = 1 To 100
                    'MsgBox(xlBook.Worksheets(1).Cells(irow, jcol).Value)

                    str = str2.Append(xlsBook.Worksheets(i).Cells(irow, jcol).Value & " ")


                Next
            Next
        Next i

        'MsgBox(str.ToString)

        xlsBook.Saved = True
        xlsBook.Application.DisplayAlerts = False
        xlsBook.Close()
        xlsapp.Application.Quit()
        'xlsBook.SaveAs(Filename:="C:\email\commaTEST")

        releaseObject(xlworksheet)
        releaseObject(xlsBook)
        releaseObject(xlsapp)

  Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As System.Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
Reply With Quote
  #2  
Old 11-30-2011, 09:36 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Hi,
Have You been able to resolve it?
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
How To select all used rows and columns and display as text
How To select all used rows and columns and display as text
How To select all used rows and columns and display as text How To select all used rows and columns and display as text
How To select all used rows and columns and display as text
How To select all used rows and columns and display as text
How To select all used rows and columns and display as text How To select all used rows and columns and display as text How To select all used rows and columns and display as text How To select all used rows and columns and display as text How To select all used rows and columns and display as text How To select all used rows and columns and display as text How To select all used rows and columns and display as text
How To select all used rows and columns and display as text
How To select all used rows and columns and display as text
 
How To select all used rows and columns and display as text
How To select all used rows and columns and display as text
 
-->