Excel - Access - VB.NET Project
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 29

Thread: Excel - Access - VB.NET Project

  1. #1
    Join Date
    Jun 2004
    Posts
    96

    Unhappy Excel - Access - VB.NET Project

    Dear all VB Guru :

    I have a new project. This project will involve Microsoft Excel File, Microsoft Access and VB.NET.

    The concept of the program is like this :

    User send 1 excel fiel to fanance manager, he has to import it to Microsoft Access database. later he will do some analyst or report base on the condition he want to define, like display by monthly basic, or diferent branch or different industry.

    But user doen't know any of the IT knowledge like datasource or ODBC.....and such process like export from Excel and import to Access database should be down in more user friendly interface ( means I have to develop a program for this purpose, which only require few click here and there and done).

    So now, the burden on me now ...... I need to write a program, which it it can read all data from Excel files. and then import the data to Acess database to selective field and table. the user just need open the program and user just click wat he wan to display and the system show the summary.


    the step by step of the program runlike this :

    1. Finance manager receive finance report in excel file from branches.
    2. user open the program i develop.
    3. user will browse for the excel to import to Access database.
    4. user click ok, and verything done by the system
    4. when finish import the record to database. user select other function, in this page, user will display the final summary in different case, like sales of branch A, branch B, February sales all the branches........

    so i think you all get the idea.....

    now. I first problem :
    is there any example out there or any guru here done this before ? Currently I am stuck on the first few step, which is detect the excel file and read the cell by cell and import to the desire table in the database.


    I wish you all can provided some step by step or eample code for me for reference.



    thanks a lot

  2. #2
    Join Date
    Mar 2001
    Location
    Australia
    Posts
    146
    OK, this code will allow you to search for an excel spreadsheet and to read all the cells.

    Code:
            Dim strFileName As String
    
            ' Create a dialog box to find the excel file
            Dim dlg As New Windows.Forms.OpenFileDialog
            dlg.Filter = "Excel Files (*.xls)|*.xls"
            dlg.FilterIndex = 0
            dlg.ShowDialog()
    
            strFileName = dlg.FileName
    
            ' Open the Workbook in excel
            Dim xlApp As New Excel.Application
            xlApp.Visible = True
            Dim xlWB As Excel.Workbook = xlApp.Workbooks.Open(strFileName)
            Dim xls As Excel.Worksheet = xlWB.Sheets(1)
    
            Dim intRows, intCols As Integer
            Dim r, c As Integer
    
            intRows = xls.UsedRange.Rows.Count
            intCols = xls.UsedRange.Columns.Count
    
            ' Scroll through all the rows and columns retrieving values.
            For r = 1 To intRows
                For c = 1 To intCols
                    MsgBox(CType(xls.Cells(r, c), Excel.Range).Text)
                Next
            Next
    
            xlWB.Close(False)
            xlApp.Quit()
            xls = Nothing
            xlWB = Nothing
            xlApp = Nothing
    To create an Access Database, check this out:

    HOW TO: Create a Microsoft Access Database Using ADOX and Visual Basic .NET


    Hope this helps,

    Nathan.

  3. #3
    Join Date
    Jun 2004
    Posts
    96

    Smile I wil try it

    Thanks,

    I will try yur code and see how it work. Your information help.
    this forum is great!

  4. #4
    Join Date
    Jun 2004
    Posts
    96

    error on Excel.Application

    Dear GungaDin,

    I have to run the code. I modified a bit and put the code inside the button fucntion, like a start button for the system to read the file.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim objExcel As New Excel.Application 'having error here
    Dim objWS As New Excel.Worksheet 'having error here
    objExcel.Workbooks.Open("C:\my folder\exceldata1.xls")
    objExcel.Visible = True 'False to perform operations without viewing excel.

    objWS = objExcel.Worksheets.Item(1) 'First sheet in workbook

    Dim MyVariable As String

    MyVariable = objWS.Cells(1, 1).Value 'A1 cell.

    MsgBox(MyVariable)

    objExcel.Workbooks.Close()
    objExcel.Quit()

    objExcel = Nothing
    objWS = Nothing
    End Sub

    temporary i just wan to try read the first cell.


    but i have some error:
    Type 'Excel.Application' is not defined.
    Type 'Excel.Worksheet' is not defined.

  5. #5
    Join Date
    Mar 2001
    Location
    Australia
    Posts
    146
    Not near a PC with .NET on it at the moment but you need to include a reference to the Microsoft Excel library.

  6. #6
    Join Date
    Nov 2004
    Posts
    8

    Re: Excel - Access - VB.NET Project

    Hi. I tried to appliy GungaDin's code, but have a problem with
    Dim dlg As New Windows.Forms.OpenFileDialog()
    The "Debugger" says: "type Windows.Forms.OpenFileDialog() is not defined"
    I thought that
    Imports System.Windows.Forms
    might fix this, but it didn't. Please advise.

    And also, GundaDin, i kinda hoped that you (or somebody else viewing this page) might sugest on how do we create an Excel sheet with a given name from VB.net. Particularly i need the vb.net to dynamicaly create new excel sheet in Book1 for every new record from DataReader.
    I have some start up (I've adapted codes from a few different sources on the Internet) :

    Dim DataReaderSQL As System.Data.SqlClient.SqlDataReader
    DataReaderSQL = SqlCommand.ExecuteReader()
    Dim i As Integer
    i = 0
    While (DataReaderSQL.Read())
    Dim strFirstName As String
    Dim strLastName As String
    strFirstName = DataReaderSQL.Item(0).ToString
    strLastName = DataReaderSQL.Item(1).ToString
    i = i + 1
    '---Call AddNewSheet(i, strFirstName, strLastName) ----
    '--- I need help with the called above procedure
    '--- The procedure creates a new Excel file so i'm getting only one Sheet
    '--- (the last one) with the required name. Beside, it can create
    '--- only 3 Sheets in total, because, i guess, by default Excel has only
    '--- Sheet1/Sheet2/and Sheet3, so for the 4th record in DataReader
    '--- i'm getting another error message
    End While
    DataReaderSQL.Close()

    Private Sub AddNewSheet(ByVal SheetNo As Integer, ByVal strFName As String, ByVal strLName As String)
    Dim AppXls As Excel.Application
    Dim ObjWB As Excel.Workbook
    Dim SheetName As String
    Dim strValue As String

    strValue = strFName & " " & strLName
    SheetName = "Sheet" & SheetNo

    AppXls = New Excel.Application()

    ObjWB = AppXls.Workbooks.Add
    ObjWB.Worksheets.Item(SheetName).Range("A1").Value = strValue

    ObjWB.SaveAs("C:\Test1.Xls")
    ObjWB.Save()
    ObjWB.Close(SaveChanges:=False)

    AppXls.Quit()
    End Sub

    Thank you in advance. I would appreciate the help alot.

  7. #7
    Join Date
    Mar 2001
    Location
    Australia
    Posts
    146

    Re: Excel - Access - VB.NET Project

    Hi Salika,

    For your Dim dlg As New Windows.Forms.OpenFileDialog() problem, You might need to include a reference to System.Windows.Forms.


    As far as your other code is concerned, your sub actually creates a new Excel Application and workbook every time it's called and then overrides your spreadsheet every time. I'd maybe try the following:

    Code:
            Dim DataReaderSQL As System.Data.SqlClient.SqlDataReader
            Dim strFirstName, strLastName As String
            DataReaderSQL = SqlCommand.ExecuteReader()
    
            ' Open your Excel application and workbook here
            Dim xlApp As New Excel.Application
            Dim xlWB As Excel.Workbook = xlApp.Workbooks.Add
            xlWB.SaveAs("C:\Test1.Xls")
            ' Loop through your reader
            While (DataReaderSQL.Read())
                strFirstName = DataReaderSQL.Item(0).ToString
                strLastName = DataReaderSQL.Item(1).ToString
                ' Add your sheets here
                Call AddNewSheet(xlWB, strFirstName, strLastName)
            End While
            DataReaderSQL.Close()
            ' Close the workbook and application
            xlWB.Close(True)
            xlWB = Nothing
            xlApp.Quit()
            xlApp = Nothing
    
    
        Private Sub AddNewSheet(ByVal xlWB As Excel.Workbook, ByVal strFName As String, ByVal strLName As String)
    
            Dim strValue As String = strFName & " " & strLName
    
            ' Add your new worksheet
            Dim xls As Excel.Worksheet = xlWB.Worksheets.Add()
    
            ' Set the Range
            xls.Range("A1").Value = strValue
            ' Set the name
            xls.Name = strValue
    
            ' Save the workbook
            xlWB.Save()
    
        End Sub
    Hope this helps,

    Nathan.

  8. #8
    Join Date
    Nov 2004
    Posts
    8

    Re: Excel - Access - VB.NET Project

    It helps! Many-many thanks! It works!
    thank you Nathan!

    Alika

  9. #9
    Join Date
    Mar 2001
    Location
    Australia
    Posts
    146

    Re: Excel - Access - VB.NET Project

    My Pleasure

  10. #10
    Join Date
    Nov 2004
    Posts
    8

    Nathan, one more question. May I?

    Your code performs the assignment perfecrly. It creates new page for every record and inserts required vlaue to required cells.
    The excel file I'm working with has to be quite complecated (with lots of formulas, lots of colors and lots of notes). Only several cells should be different for each record (such as last/first name, and some more personal information). So
    i'm trying to apply an existing tamplet for each excel sheet (for each record from the DataReader), updating just some cells. i tried to adapt your code with this purpose, but only the last record happence to be with a tamplet data with proper name and a proper value for Last/First Name cells. All preceding records appeared in the excel book as Sheet2, Sheet3,..., SheetN vwith no vlaue in it.
    Here is the code. Can't find what is my problem. Can you help?

    Dim DataReaderSQL As System.Data.SqlClient.SqlDataReader
    Dim strFirstName As String
    Dim strLastName As String
    Dim strPathMatrix As String = "C:\Tamplet.xls"
    Dim strPathCoverSheet As String = "C:\Final.Xls"

    DataReaderSQL = SqlCommand.ExecuteReader()

    ' Open the Excel application and workbook
    Dim xlApp As New Excel.Application()

    Dim xlWB As Excel.Workbook = xlApp.Workbooks.Open(strPathMatrix)
    xlWB.SaveAs(strPathCoverSheet)

    Dim xls As Excel.Worksheet = xlWB.Sheets(1)

    While (DataReaderSQL.Read())
    strFirstName = DataReaderSQL.Item(0).ToString
    strLastName = DataReaderSQL.Item(1).ToString
    Call My_AddNewSheet(xlWB, xls, strFirstName, strLastName)
    End While

    DataReaderSQL.Close()


    Private Sub My_AddNewSheet(ByVal xlWB As Excel.Workbook, ByVal xls As Excel.Worksheet, ByVal strFName As String, ByVal strLName As String)
    Dim strValue As String = strFName & " " & strLName

    ' Add a new worksheet
    Dim xlsNew As Excel.Worksheet = xlWB.Worksheets.Add()
    xlsNew = xls
    ' Set the Range
    xlsNew.Range("B10").Value = strLName
    xlsNew.Range("D10").Value = strFName

    ' Set the name
    xlsNew.Name = strValue

    ' Save the workbook
    xlWB.Save()
    'Console.WriteLine("End AddSheet: " & strValue)

    End Sub

    Thank you again.

  11. #11
    Join Date
    Mar 2001
    Location
    Australia
    Posts
    146

    Re: Excel - Access - VB.NET Project

    OK, I think I can see what you're doing here. I assume that the first sheet in your template spreadsheet contains your formatting. Try this for your procedure:

    Code:
        Private Sub My_AddNewSheet(ByVal xlWB As Excel.Workbook, ByVal xls As Excel.Worksheet, ByVal strFName As String, ByVal strLName As String)
            Dim strValue As String = strFName & " " & strLName
    
            Dim xlsNew As Excel.Worksheet
    
            ' Copy your template sheet
            xls.Copy(After:=xlWB.Sheets(xlWB.Sheets.Count))
    
            ' Select the last sheet (this will be your new sheet)
            xlsNew = xlWB.Worksheets(xlWB.Worksheets.Count)
    
            ' Set the Range
            xlsNew.Range("B10").Value = strLName
            xlsNew.Range("D10").Value = strFName
    
            ' Set the name
            xlsNew.Name = strValue
    
            ' Save the workbook
            xlWB.Save()
            'Console.WriteLine("End AddSheet: " & strValue)
    
        End Sub
    Hope this helps,

    Nathan.

  12. #12
    Join Date
    Nov 2004
    Posts
    8

    Re: Excel - Access - VB.NET Project

    Nathan, that was great!
    Your xls.Copy(After:=xlWB.Sheets(xlWB.Sheets.Count))
    does the magic.
    Thank you very much! Appreciate your help allot!

    Alika

  13. #13
    Join Date
    Dec 2004
    Posts
    2

    Excel - CheckBox - VB.NET Project

    Hi all,

    i have created an Application in VB.NET which reads the values from the Cells and Text boxes and Rectangles from Excel Sheet but i m not able to read the check box value, Like its Check or Uncheck, i m taking all the values and uploading them into Database,

    So please i just need to know how can i get the check box (check or uncheck) value.


    Your Help on this matter will be highly appreciated.


    Thanks
    Regards

    Azhar Khan

    System Analyst

  14. #14
    Join Date
    Dec 2004
    Posts
    2

    Re: Excel - Access - VB.NET Project

    Well i tried the following code by just guessing. When i type Excel and check its properties after clicking the dot, does not show any proprty name checkbox, i just guess it and typed check box and it works , but it returns some -ve value when its unchecked and 1 value when its checked, if someone has better solution then please share with me.

    Dim r As Excel.CheckBox

    Dim str_value As String
    Dim name, text, visible, caption, index, printObejct, Placement As String

    For Each r In oExcel.ActiveSheet.checkboxes
    str_value = r.Value
    name = r.Name
    text = r.Text
    visible = r.Visible
    caption = r.Caption
    'index = r.Index
    'printObejct = r.PrintObject
    'Placement = r.Placement

    Next r

    Thanks
    Regards

  15. #15
    Join Date
    Dec 2004
    Location
    Canada -- Windsor, Ontario
    Posts
    7

    Re: Excel - Access - VB.NET Project

    Hi All

    I'm doing something very similar and I hope much simpler.

    I'm creating a booking system and one of the current problems with the current booking system is that if the user walks way from the computer just before the session is over (with a 5 min grace perriod) The booking system will reboot the system and everything the user was woking on is lost (Mainly Word and Excel) I want to add the code to save all the active documents in Word and all the active Workbooks in Excel (if there are any open).

    Word seems to be easier to code, but only if you use marco's within word like I did in this example (macro = "newDocName")

    Dim oWord As Word.Application

    Try

    oWord = GetObject(, "Word.Application")
    oWord.Application.Visible = True

    'Run the macros.
    oWord.Run("newDocName")

    'Quit Word.
    oWord.Quit()
    System.Runtime.InteropServices.Marshal.ReleaseComObject(oWord)
    oWord = Nothing

    Catch ex As Exception

    End Try

    ============================================

    Mircosoft Word Macro
    -----------------------------
    Sub newDocName()
    '
    ' newDocName Macro
    ' Macro recorded 12/14/2004 by Leddy Library

    On Error GoTo stError

    ActiveDocument.SaveAs FileName:="Doc2.doc", FileFormat:=wdFormatDocument, _
    LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _
    :="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
    SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
    False
    ActiveDocument.Close

    stError: Application.Quit

    End Sub

    ================================

    In Excel you can not create a global marco like in Word or at least I can't figure it out. If I could do this without the macro's it would be better.

    Could anyone Please Help --- Thanks, sorry for the long post

    GB

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center