hello,
I have been tasked with writing a VB.net application that can take excel spreadsheets and convert the entries into the correct database entries (in an Access database). I am using VB because of its ease of use and quick results (and because I've done it before). However, I have been doing some research to determine what the best way to accomplish this is and that is how I found the Office PIAs. I have not used them before and don't know what their limitations or advantages are. There's not a lot online about them and I think they might make it easier to access information in my spreadsheets.
So my question is, " How can I use or should I use the Office PIAs to directly access the spreadsheets I need to tear apart "
Another question I have is, "Do any of you know what advantages /disadvantages OLE has over OLEDB " I have used OLEDB in the past but sometimes it has problems with data types when performing queries from inside VB (because the query is a string and the DB wants data types). Any input
Thanks guys,
VB.Net, Access, Excel, and the Office PIAs
ProdigySoft
I am developing both the access app and the VB app. The only data I have is on the spreadsheets. Problem: I have multiple spreadsheets and only need specific info from each.
I am using VB because... I am writing an analysis program to analyze the data I get from the spreadsheets. The amount of data I have prohibits me from simply using excel VBA and the database I have designed will correct the issues with the spreadsheets.
The other reason is I am writing a login authentication so that no one has access to the sensitive data in the DB.
I did not know I could connect to excel with an oledb connection though that is why I was looking in the Office PIAs. But if you can connect to an xls spreadsheet with oledb then I am good to go.
Thanks for the input....
ghawkes
You can do both using the "OleDb" namespace, i.e.
'---------------------------------------------------------
' Excel File:
'
Dim sFile As String = "c:\example.xls"
Dim sSheetName As String = "Sheet1"
Dim con As OleDb.OleDbConnection
Dim cmd As OleDb.OleDbCommand
' Create an OLEDB connection to the Excel Workbook
con = New OleDb.OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFile & _
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")
' Open the connection
con.Open()
' Create a Command object to get all data from the Worksheet
cmd = New OleDb.OleDbCommand("SELECT * FROM [" & sSheetName & "$]", con)
' Execute the command returning a Data Reader
Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader()
' Enuermate all the rows of data in the sheet
While reader.Read()
' Display the contents of the first column
Console.WriteLine(reader.GetString(0))
End While
' Close the reader
reader.Close()
' Close the connection
con.Close()
'----------------------------------------------------------
'----------------------------------------------------------
' Access MDB:
'
Dim sDBPath As String = "c:\example.mdb"
Dim sUser As String = "admin"
Dim sPassword As String = ""
Dim sTable As String = "MyTable"
Dim con As OleDb.OleDbConnection
Dim cmd As OleDb.OleDbCommand
' Create an OLEDB connection to the Access Database
con = New OleDb.OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sDBPath & ";" & _
"User Id=" & sUser & ";" & _
"Password=" & spassword & ";")
' Open the connection
con.Open()
' Create a Command object to get all data from the table
cmd = New OleDb.OleDbCommand("SELECT * FROM [" & sTable & "]", con)
' Execute the command returning a Data Reader
Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader()
' Enuermate all the rows of data in the table
While reader.Read()
' Display the contents of the first column
Console.WriteLine(reader.GetString(0))
End While
' Close the reader
reader.Close()
' Close the connection
con.Close()
End Sub
'----------------------------------------------------------
Hey thanks again DMAN ... I got it now
jzfredricks
If you have the access application the quickest method to complete your task is from the access application choose "File" and then "Get External Data" and then "Import". It has a special routine for importing Excel Spreadsheets. (If it is repetitive then you can setup a macro from within Access)
However if you are adamant about using VB for one reason or another then...you simply need to use two oledb connections and transfer the data by selecting from the spreadsheet connection and inserting to the Access connection...