StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<pre><code> Try Cursor = Cursors.WaitCursor 'can't populate gridview with an open file using OLEDB - need to export the Budget sheet to a new file, close it, connect with OLEDB, then delete the temp Budget.xls file just created 'copy Budget sheet to new workbook, delete unneeded columns, save file as Budget.xls, close workbook Dim filenm = "W:\TOM\ERIC\Budget Temp\Budget.xls" StatVar.xlApp.Sheets("Budget").Copy() StatVar.xlApp.ActiveWorkbook.Sheets("Budget").Columns("C:DY").Delete() StatVar.xlApp.ActiveWorkbook.SaveAs("W:\TOM\ERIC\Budget Temp\Budget.xls") StatVar.xlApp.ActiveWorkbook.Close(True) 'connect to Excel data source and set gridview equal to dataset (entire sheet should be visible in gridview) Dim MyConnection As System.Data.OleDb.OleDbConnection Dim MyCommand As System.Data.OleDb.OleDbDataAdapter Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & filenm & ";Extended Properties=""Excel 12.0;HDR=YES;Readonly=False"";" 'may need to use different MS provider and lower OLEDB for .xls files (Microsoft.Jet.OLEDB4.0...Excel 8.0) kind of sketchy though MyConnection = New System.Data.OleDb.OleDbConnection(connstring) 'create a new data connection to Excel data source MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Budget$A:F]", MyConnection) 'query the sheet - select all data in columns A:F MyCommand.TableMappings.Add("Table", "Table") 'map data selection as table StatVar.DtSet1 = New System.Data.DataSet 'create new data set MyCommand.Fill(StatVar.DtSet1) 'fill data set with table Form14.DataGridView1.DataSource = StatVar.DtSet1.Tables(0) 'populate gridview with data set table MyConnection.Close() 'delete temporary Budget.xls file created at beginning of procedure and show Budget Codes form File.Delete(filenm) Form14.TxtBoxAutoCode.Text = StatVar.xlApp.Sheets("Budget").Range("EU2").Text Form14.ShowDialog() Catch exc As Exception MessageBox.Show("There was a problem loading this database. Please contact an administrator if the problem continues." & vbNewLine & vbNewLine & "Error: " & exc.Message) Finally Cursor = Cursors.Default End Try </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload