Dim oXLApp As Excel.Application 'Declare the object variables
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet
oXLApp = New Excel.Application 'Create a new instance of Excel
oXLBook = oXLApp.Workbooks.Add
oXLSheet = oXLBook.Worksheets(1)
oXLApp.Visible = True
Dim sql As String = "" & BuilderFilter() & "" 'The SQL Select statement used to populate the datagridview control when the search button is clicked
Dim cn As New ADODB.Connection()
Dim rs As New ADODB.Recordset()
Dim cnStr As String
Dim cmd As New ADODB.Command()
cnStr = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;Integrated Security=SSPI;"
cn.Open(cnStr)
cmd.ActiveConnection = cn
cmd.CommandText = sql
rs = cmd.Execute
oXLSheet.Range("A1").CopyFromRecordset(rs)
rs.Close()
cn.Close()
'Insert column headers
oXLSheet.Rows(1).Insert()
oXLSheet.Cells(1, 1).Value = "Blanket Order No"
oXLSheet.Cells(1, 2).Value = "Customer Blanket Order No"
oXLSheet.Cells(1, 3).Value = "Status"
oXLSheet.Cells(1, 4).Value = "Account"
oXLSheet.Cells(1, 5).Value = "Blanket Order Date"
oXLSheet.Cells(1, 6).Value = "Blanket Order End Date"
oXLSheet.Cells(1, 7).Value = "Blanket Create Date"
oXLSheet.Cells(1, 8).Value = "Blanket Closed Date"
oXLSheet.Cells(1, 9).Value = "Item Number"
oXLSheet.Cells(1, 10).Value = "Quantity"
oXLSheet.Cells(1, 11).Value = "Minimum Stock Qty"
oXLSheet.Cells(1, 12).Value = "Item Status"
oXLSheet.Cells(1, 13).Value = "Item Closed Date"
oXLSheet.Cells(1, 14).Value = "Notes"
oXLSheet.UsedRange.Borders.LineStyle = 1
oXLApp.Columns.AutoFit()
oXLSheet.Range("A1:N1").Font.Bold = True
oXLSheet.Range("E:E").NumberFormat = "MM/DD/YYYY"
oXLBook = Nothing 'Disconnect from Excel (let the user take over)
oXLApp = Nothing
oXLSheet = Nothing
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.