I am new to VB, just got started not long ago, and I am happy to be making progress.
However, I am more new to VB forms when connects to SQL server and allows user to interact with it to query out whatever data they wants into excel.
It begins like this, I've already created a userform that has checkbox (> than, < than), a textbox (input a number), and 2 other checkbox (male, female) and a combobox (state). I also already have the data in the SQL Server database.
What I am trying to do and is still trying is to allow users to interact with the form by checking the checkboxes, making selection in the combobox and inputting a number in the textbox and click on a button to run the VB program to export the requested data into Excel (my challenge is - it can export it into an Excel file that has already been created and saved in a directory or to export it into a newly created Excel file that has not been save yet (kind of like a pop-up).
For example - a user checks > than, and enter number 25 (btw this is age), and checks female and in combobox selects NY and click on a button. The program should query out, in this case, female over 25 that lives in NY and export it into Excel as a pop-up or an excel file that is already saved in a directory. I have been doing some research on this but hasn't made much progress as I am new to forms, connection and extraction. My code below creates an Excel file in a directory and is trying to query out the data into the save Excel file. My query is below too. Please advise !
Imports System.IO
Imports excel = Microsoft.office.interop.Excel
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Module module1
Dim myconnection As SqlConnection
Dim mycommand As SqlCommand
Sub main()
Dim xlapp = New excel.application
xlapp.visible = True
Dim xlwb As excel.workbook
Dim xlws As excel.worksheet
Dim path As String = "C:\users\t\"
Dim excel_name As String = "zp"
xlwb = xlapp.workbooks.add()
xlws = xlwb.sheets("sheet1")
xlwb.saves(path & excel_name)
xlapp.save()
xlapp.quit()
Using myconnection As New SqlConnection("data source =afe;initial catalog=zp;integrated securitytrue")
myconnection.Open()
Dim mycommand As New SqlCommand("insert into openrowset('Microsoft.ace.oledb.12.0','excel 12.0; database=zp:\c:users\dek\rep\zp.xlsx;','SELECT * FROM [Sheet1$]') select * from mem_TBL", myconnection)
End Using
End Sub
End Module
this is my query base on user selection as example.
SELECT a.z, a.ad, a.ag, a.ret, a.tot, a.wgt
FROM mtbl a INNER JOIN zTBL b ON a.z = b.zp
WHERE a.age > 25 AND a.ad = "NY" AND a.ret ="female"
This is the approach I use when exporting to Excel: I create a template of the Excel file that I will generate and save it in a fixed folder. When I export to excel, I:
delete the temporary file
Private Sub ExportToExcel()
Using myconnection As New SqlClient.SqlConnection("data source=afe;initial catalog=zp;integrated securitytrue")
myconnection.Open()
Dim mycommand As New SqlClient.SqlCommand("SELECT a.z, a.ad, a.ag, a.ret, a.tot, a.wgt FROM mtbl a INNER JOIN zTBL b ON a.z = b.zp WHERE a.age > @age AND a.ad = @state AND a.ret = @gender", myconnection)
mycommand.Parameters.AddWithValue("@age", 25)
mycommand.Parameters.AddWithValue("@state", "NY")
mycommand.Parameters.AddWithValue("@gender", "female")
Dim dataset As New DataSet
Dim adapter As New SqlClient.SqlDataAdapter(mycommand)
adapter.Fill(dataset, "data")
Dim xlapp = New Microsoft.Office.Interop.Excel.Application
xlapp.visible = True
Dim xlwb As Microsoft.Office.Interop.Excel.Workbook
Dim xlws As Microsoft.Office.Interop.Excel.Worksheet
Dim templatePath As String = "<path to template file>"
Dim path As String = "C:\users\t\"
Dim excel_name As String = "zp"
Dim tempFIle As String = templatePath & "\NAME OF YOUR TEMPLATE FILE INCLUDING EXTENSION"
xlwb = xlapp.Workbooks.Open(tempFIle)
xlws = CType(xlwb.Worksheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet)
Dim rowIndex As Integer = 0
For Each row As DataRow In dataset.Tables(0).Rows
' since you alrady have a template,
' you already know the cell mapping of each column
' in your template file.
' Excel uses Row, Column to map cells and is 1-based
rowIndex += 1
xlapp.Cells(rowIndex, 1).Value = row("<name of column 1>")
xlapp.Cells(rowIndex, 2).Value = row("<name of column 2>")
xlapp.Cells(rowIndex, 3).Value = row("<name of column 3>")
xlapp.Cells(rowIndex, 4).Value = row("<name of column 4>")
'.
'.
'xlapp.Cells(rowIndex, N).Value = row("<name of column N>")
Next
xlapp.DisplayAlerts = False
xlwb.SaveAs(path & excel_name)
xlwb.Close()
xlapp.DisplayAlerts = True
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlwb)
xlapp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlapp)
System.IO.File.Delete(tempFIle)
End Using
End Sub