-
August 11th, 2004, 01:23 AM
#1
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
-
August 11th, 2004, 07:16 PM
#2
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.
-
August 11th, 2004, 07:32 PM
#3
I wil try it
Thanks,
I will try yur code and see how it work. Your information help.
this forum is great!
-
August 23rd, 2004, 09:35 PM
#4
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.
-
August 23rd, 2004, 10:27 PM
#5
Not near a PC with .NET on it at the moment but you need to include a reference to the Microsoft Excel library.
-
November 17th, 2004, 01:10 PM
#6
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.
-
November 17th, 2004, 05:56 PM
#7
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.
-
November 18th, 2004, 04:01 PM
#8
Re: Excel - Access - VB.NET Project
It helps! Many-many thanks! It works!
thank you Nathan!
Alika
-
November 18th, 2004, 06:27 PM
#9
Re: Excel - Access - VB.NET Project
My Pleasure
-
November 19th, 2004, 01:24 PM
#10
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.
-
November 19th, 2004, 05:04 PM
#11
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.
-
November 24th, 2004, 10:23 AM
#12
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
-
December 7th, 2004, 04:39 AM
#13
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
-
December 7th, 2004, 05:13 AM
#14
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
-
December 15th, 2004, 05:07 PM
#15
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|