Excel - Access - VB.NET Project

[marcus78]

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

[GungaDin]

OK, this code will allow you to search for an excel spreadsheet and to read all the cells.

        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.

[marcus78]

Thanks,

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

[marcus78]

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.

[GungaDin]

Not near a PC with .NET on it at the moment but you need to include a reference to the Microsoft Excel library.

[Salika]

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.

[GungaDin]

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:

        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.

[Salika]

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

Alika

[GungaDin]

My Pleasure

[Salika]

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.

[GungaDin]

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:

    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.

[Salika]

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

[khanas]

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

[khanas]

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

[GB_Whale]

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