Excel Automation {copy data from Excel to database table}
Excel Automation {copy data from Excel to database table}
Excel Automation {copy data from Excel to database table}
Excel Automation {copy data from Excel to database table}
Excel Automation {copy data from Excel to database table}
Excel Automation {copy data from Excel to database table} Excel Automation {copy data from Excel to database table} Excel Automation {copy data from Excel to database table} Excel Automation {copy data from Excel to database table} Excel Automation {copy data from Excel to database table} Excel Automation {copy data from Excel to database table} Excel Automation {copy data from Excel to database table} Excel Automation {copy data from Excel to database table}
Excel Automation {copy data from Excel to database table} Excel Automation {copy data from Excel to database table}
Excel Automation {copy data from Excel to database table}
Go Back  Xtreme Visual Basic Talk > > > Excel Automation {copy data from Excel to database table}


Reply
 
Thread Tools Display Modes
  #1  
Old 02-17-2005, 04:27 AM
Nick_1 Nick_1 is offline
Freshman
 
Join Date: Jan 2005
Posts: 44
Default Excel Automation {copy data from Excel to database table}


Hey all I need a way to copy all entrys in an excel sheet and paste them in to a database table. I just cant sort out the code to select all the cells and copy them. Oh yea the excel file is a new one and a different size every day. I can open it with automation from vb.net just how the hell do i copy it and paste it into my access database (table1).
Dim Exlapp As New Excel.Application
Code:
     Dim filename As String


        If OpenFileDialog1.ShowDialog = DialogResult.OK Then
            filename = OpenFileDialog1.FileName

        End If
        Exlapp.Workbooks.Open(filename)

        Exlapp.Visible = True
Reply With Quote
  #2  
Old 02-17-2005, 05:57 AM
herilane's Avatar
herilaneExcel Automation {copy data from Excel to database table} herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

You would normally use ADO.NET to do this, and not Excel automation + copy-paste.

MSDN KB article about using ADO.NET with Excel workbooks

I've moved this to the Database forum where this is more likely to get the right people's attention.
Reply With Quote
  #3  
Old 02-20-2005, 10:33 AM
Nick_1 Nick_1 is offline
Freshman
 
Join Date: Jan 2005
Posts: 44
Default excel to access

hi all thanks for the link, ive learned lots but am still a bit stuck.i use an openfiledialog to chose an excel file i then pass the file name to the jet open string for the excel file.so far so good.I am not sure of the code to send this excel work sheet to my already set up database(access). i will need to clear all records in the data base and fill it with my worksheet each day. this works with copy and pasting i just want to automate it. any help or code snippets would be a big help
Reply With Quote
  #4  
Old 02-21-2005, 06:01 PM
MKoslof's Avatar
MKoslofExcel Automation {copy data from Excel to database table} MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

1) To delete records from the database you can use an OLEDBCommand object and fire a DELETE command. If you are using a DataAdapter,you can update the dataAdapter, passing a dynamic .deleteCommand, or if you are not using a DataAdapter,you can implement the .ExecuteNonQuery method of the command object

Code:
"DELETE * FROM myTable"

Now to insert records from Excel to Access you can use the Jet provider. You can make a direct connection between Excel and Access. Say you want to import the entire worksheet into a new table:

Code:
Dim sConnect as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.xls;Extended Properties=Excel 8.0;" _ & "Persist Security Info=False" Dim sInsert as string = "INSERT INTO [T1] IN 'C:\mydb.mdb' SELECT * FROM [Sheet$]" Dim cn as New OleDbConnection(sConnect) cn.Open Dim myCmd as New OleDbCommand With myCmd .Connection = cn .CommandText = sInsert .CommandType = CommandType.CommandText End With myCmd.ExecuteNonQuery() cn.close cn.dispose myCmd.Dispose
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #5  
Old 02-23-2005, 01:38 AM
Nick_1 Nick_1 is offline
Freshman
 
Join Date: Jan 2005
Posts: 44
Default excel to database

Code:
 'clear old data from database
        DataSet11.Clear()
        OleDbDataAdapter1.Update(DataSet11)

        'dump excel file in to database

        Dim sConnect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test1.xls;Extended Properties=Excel 8.0;"

        Dim sInsert As String = "INSERT INTO [Table1] IN 'C:\work.mdb' SELECT * FROM [Sheet1]"

        Dim cn As New OleDbConnection(sConnect)

        cn.Open()

        Dim myCmd As New OleDbCommand

        With myCmd
            .Connection = cn
            .CommandText = sInsert
            .CommandType = CommandType.StoredProcedure
        End With

        myCmd.ExecuteNonQuery()

        cn.Close()
        cn.Dispose()
        myCmd.Dispose()
should the
Code:
  .CommandType = CommandType.CommandText
read
Code:
  .CommandType = CommandType.storedprocedure
Also the connection string is creating a new(corrupt) excel file called test1 instead of opening the existing one. Excel as a datasource is a pain in the nuts. Again any help would be cool.

Last edited by Nick_1; 02-23-2005 at 02:46 AM.
Reply With Quote
  #6  
Old 02-23-2005, 03:23 AM
herilane's Avatar
herilaneExcel Automation {copy data from Excel to database table} herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

You forgot the $ sign at the end of the sheet name. Should be
Code:
Dim sInsert As String = "INSERT INTO [Table1] IN 'C:\work.mdb' SELECT * FROM [Sheet1$]"
Also, please use [vb][/vb] instead of [code] tags to post VB code. Makes it easier to read.
Reply With Quote
  #7  
Old 02-23-2005, 08:01 AM
Nick_1 Nick_1 is offline
Freshman
 
Join Date: Jan 2005
Posts: 44
Default

Code:
'clear old data from database DataSet11.Clear() OleDbDataAdapter1.Update(DataSet11) dim filename as string if open filedialog1.showdialog=dialogresult.ok then filename=openfiledialog1.filename end if 'dump excel file in to database Dim sConnect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test1.xls;Extended Properties=Excel 8.0;" Dim sInsert As String = "INSERT INTO [Table1] IN 'C:\work.mdb' SELECT * FROM [Sheet1]" Dim cn As New OleDbConnection(sConnect) cn.Open() Dim myCmd As New OleDbCommand With myCmd .Connection = cn .CommandText = sInsert .CommandType = CommandType.StoredProcedure End With myCmd.ExecuteNonQuery() cn.Close() cn.Dispose() myCmd.Dispose()
I want the user to chose a file in the dialog and have it dump into the database but this code is just creating another file in the source string path call test1. I dont get it.
Also would i just put the file name veriable in the source string to alow the user to chose thanks for the help.
Reply With Quote
  #8  
Old 02-26-2005, 04:32 PM
MKoslof's Avatar
MKoslofExcel Automation {copy data from Excel to database table} MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

The reason it is creating a new file is Test1.XLS in that given directory doesn't exist..so it is creating it for you.

If you want the user to select the workbook you can prompt via a FileOpen dialog or whatever method you want, and then pass the full path and file name to the sql string.

And you only use the commandType.Storedprocedure if you are referencing a stored database function, such as Stored Procedure in SQL Server or a parameterized query in MS Access. As far as I know, there are no stored data access methods in Excel, since this is not a database engine that understands this type of logic.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #9  
Old 02-28-2005, 03:51 AM
Nick_1 Nick_1 is offline
Freshman
 
Join Date: Jan 2005
Posts: 44
Default

Quote:
Originally Posted by MKoslof
The reason it is creating a new file is Test1.XLS in that given directory doesn't exist..so it is creating it for you..
Test1.XLS this file does exist still it creates a new corrupt one. WHY?
Also any futher help on the full connection path. Does the openfileDialog give the full file path or just the filename. I think its just the filename.

Code:
Dim sConnect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=""&filename&"";Extended Properties=Excel 8.0;persist security info=false"
The source is giving me hassle i am getting this error when i choose an excel file form the open file dialog
An unhandled exception of type 'System.Data.OleDb.OleDbException'
occurred in system.data.dll
I changed to this
Code:
Dim sConnect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data source="c:\"&filename&"";Extended Properties=Excel 8.0;persist security info=false"
but it dont work either. Nick_1
Reply With Quote
  #10  
Old 02-28-2005, 04:29 PM
MKoslof's Avatar
MKoslofExcel Automation {copy data from Excel to database table} MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

If you attempt to open an existing workbook with the proper path provided, it should work fine.

To return the name and path from the dialog, use:

Code:
Dim fSave As New OpenFileDialog fSave.ShowDialog() MessageBox.Show(fSave.FileName.ToString())
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
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
Excel Automation {copy data from Excel to database table}
Excel Automation {copy data from Excel to database table}
Excel Automation {copy data from Excel to database table} Excel Automation {copy data from Excel to database table}
Excel Automation {copy data from Excel to database table}
Excel Automation {copy data from Excel to database table}
Excel Automation {copy data from Excel to database table} Excel Automation {copy data from Excel to database table} Excel Automation {copy data from Excel to database table} Excel Automation {copy data from Excel to database table} Excel Automation {copy data from Excel to database table} Excel Automation {copy data from Excel to database table} Excel Automation {copy data from Excel to database table}
Excel Automation {copy data from Excel to database table}
Excel Automation {copy data from Excel to database table}
 
Excel Automation {copy data from Excel to database table}
Excel Automation {copy data from Excel to database table}
 
-->