populating data into an empty column
I am working on a project that uses an Excel spreadsheet and an Access db with 3 tables. I created the dataset and connected to each table and the spreadsheet making 4 tables in the dataset. I need to add a column into the middle of the "Excel" table (i.e. at column(7) of 12). Then, I need to put data into each row, in the new column, based on the data from column (3) but derived from one of the Access tables.
Any ideas on any of these steps would be appreciated. BTW I'm fairly new to VB.NET and use VS2005 and don't know the language very well.
Thanks - ahead of time.
It's a tough call, there are so many ways to get things done which is why we set up this forum and why I want to help.
Let me ask this first; have you or are you using VSTO (Visual Studio Tools for Office) to maniuplate the Excel Spreadsheet? If you haven't tried this yet, you might find your task is much easier. If you don't have this product or don't want to go that route, then you are on the right track;
It sounds like your Exel spreadsheet is static (i.e. it just has some of the data you need) and then needs to be update statically as well (i.e. at the one column).
You need to grab an instance of the Excel spreadsheet as an object
add a reference to your project :Microsoft.Office.Interop.Excel
Dim xl As New Microsoft.Office.Interop.Excel.Application()
Dim excelSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim excelWorkbook As Microsoft.Office.Interop.Excel.Workbook
Dim workbookPath As String = "c:\book1.xlsx"
excelWorkbook = xl.Workbooks.Open(workbookPath)
excelSheet = excelWorkbook.Sheets(1)
From here you can manupulate the spreadsheet and use the columns object to insert. I hope this helps.
I don't have VSTO. I'll download it and have a look.
In the meantime here is some of my code and a little description of what I want to accomplish.
A monthly report is downloaded from another database application in the Excel format. The data is incomplete. I created an Access db with three tables that I need to reference, in order to complete the monthly report. The program connects to this db and gives me 3 tables in the new dsataset..I want to add the downloaded Excel spreadsheet to the dataset, giving me four tables.
I then want to query the dataset and create an output that combines all the information I need into a single, multipage report.
This process requires we insert a column into the fourth table, name it, and populate it with the "Description" of the object from one of the tables where the "object codes" are the same.
The pseudo Code might be something like this:
(i.e. if objCode.descriptionTable = objCode.downloadTable then ... the description from the descriptionTable goes into the description.cell of the downloadTable).
(I also thought I might create a 5th table from the querry and save it as an excel workbook rather than trying to add a column and data into an existing table)
Since the application will be distributed to several secretaries, I use this code below to build the Excel connection string. This is to allow each user to save the original Excel file wherever they want and the connection string be dynamically built.
*************************************************
Public
Sub getXLDatafile()
'Declare openFileDialog object
Dim objOpenFileDialog As New OpenFileDialog
'set theOpenFileDialog properties
With objOpenFileDialog
.Filter = "Excel workbook (*.xls)|*.xls|All files (*.*)|*.*"
.FilterIndex = 1
.Title = "Open Excel workbook"
End With
'Show the dialog
If objOpenFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
'if the open button was clicked, then load the file name selected
LabelXL.Text = objOpenFileDialog.FileName
End If
'clean up
objOpenFileDialog.Dispose()
objOpenFileDialog = Nothing
End Sub
**************************************************
Then I use LabelXL.Text to build this connection string and finish building the DataSet.
**************************************************
'set the excel connection string using string from above method
strCNXL = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & LabelXL.Text & "; Extended Properties=Excel 8.0;")
'initialize a new instance of the OleDbConnection Class
objCN = New OleDbConnection(strCNXL)
' use the openConnection() code for all connections
openConnection()
'return here from openConnection sub
objDA = New OleDbDataAdapter
objDS = New DataSet("budgetSummary")
SQLSTMT = "SELECT * FROM [GalaxyPrint$];" '(galaxyPrint is the legacy app.)
'load GalaxyPrint (Excel) table into MonthlyData table object
objCMD = New OleDbCommand(SQLSTMT, objCN)
objDA.SelectCommand = objCMD
Try
objDA.Fill(objDS, "tblMonthlyData")
grdGalaxyPrint.DataSource = objDS
grdGalaxyPrint.DataMember = "tblMonthlyData"
grdGalaxyPrint.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
objCMD.Dispose()
objCMD = Nothing
End Sub
***************************************************
So now that I have the DataSet and four tables, I'm stuck as to what to do next because I don't have much VB experience.
The report is currently in Excel but doesn't need to be.
I'll have some more questions I'm sure.
Thanks Again,
Dave
