Question VBA to VB.NET output to Excel

[akomarek]

Hi,

I am learning VB.NET 2005 on my own and trying to convert an existing application in Access 2003 to .NET. I copied the code over and started with 63 errors; I have managed to work it down to only 3. These last 3 are giving me some problems. I'm not sure how to output to Excel from .NET like I was from Access. Here is my VBA code:

' Start Microsoft Excel and create a new Worksheet object.
strXLPath = "c:\Database\LIMS.xls"
xlWrksht = CreateObject("Excel.Sheet")
DoCmd.OutputTo(acOutputQuery, "DateIDAnl", acFormatXLS, _ strXLPath, True)

Sheet = xlWrksht.Workbooks.Open(strXLPath).Sheets(1)

' Make Excel visible
xlWrksht.Visible = True

Any suggestions, I'm working with MS Office 2003. Also, will this work if I move this application to an Intranet location? Everyone uses the same software as a standard at our locations.

Thanks in advance,
Al

 

[Tom]

What are the error messages saying and do you have a reference to Excel in your project?

 

[akomarek]

Error messages

First error message is: Name 'DoCmd' is not declared.
Second error message is: 'acOutputQuery' is not declared.
Third error message is: 'acFormatXLS' is not declared.

 

[Tom]

Well I dont know what your coding looks like but I can say you need to declare some of your objects...

If ya can provide the snippets where these error are, I can better help show you how to declare them. (double click on the errors to go to the problem.)

 

[akomarek]

The errors are in the code that I had in the original post. They all appear in one line; the "DoCmd.OutputTo(..." line. Here it is with a bit more code before it, to the end of the Sub:

sqlCmd.Connection = sqlConn
sqlCmd.CommandText = strSQL
DateIDAnl = sqlCmd.ExecuteReader

' Start Microsoft Excel and create a new Worksheet object.
strXLPath = "c:\Database\LIMS.xls"
xlWrksht = CreateObject("Excel.Sheet")
DoCmd.OutputTo(acOutputQuery, "DateIDAnl", acFormatXLS, _ strXLPath, True)

Sheet = xlWrksht.Workbooks.Open(strXLPath).Sheets(1)

' Make Excel visible
xlWrksht.Visible = True

' close the connection
sqlConn.Close()

Me.LstAnl.Items.Clear() ' Refresh form objects
Me.LstClnt.Items.Clear()
Me.LstClnt.Enabled = True
Me.GroupA.Checked = "No"
Me.ChkAllClnt.Checked = "No"
Me.ChkAllAn.Checked = "No"
Me.LstAnl.Enabled = True
End Sub

 

[Tom]

First error message is: Name 'DoCmd' is not declared.
Second error message is: 'acOutputQuery' is not declared.
Third error message is: 'acFormatXLS' is not declared.

Dim C1 As Date, C2 As Date
Dim CID As Object, varListItem As Object
Dim strSQL As String, strXLPath As String
Dim StrSins As String, txtSinList As String
Dim StrClnt As String, txtClList As String
Dim SinSub As Integer, Ctr As Integer
Dim Sheet As Object, xlWrksht As Object
Dim sqlConn As New SqlConnection
Dim sqlCmd As New SqlCommand
Dim DateIDAnl As SqlDataReader
Dim AnTest As Integer

In the variable declarations above, where are 'DoCmd', 'acOutputQuery' , 'acFormatXLS'?

 

[akomarek]

This was a "command line" in Access VBA that would output the file to Excel; I wouldn't know what to declare them as... because this is VBA that I am trying to convert to VB.NET. I have gone through the rest of my code that I copied over from the Access application and converted it over to .NET. It's just this one line that I'm having trouble converting.

I guess what I need to know is what does .NET use instead of the "DoCmd" in VBA and is there a similar output line, ie. "OutputTo" for Excel that I can use in .NET? I have been unable to find one with my limited knowledge of .NET.

Thank you very much for your time helping me with this, I truly appreciate it.

Al

 

[Tom]

Oh, well there isnt any built in functionality to automatically export to Excel.

You have a few choices, you can add a reference to your project to include Access and then declare your DoCmd object. Add a reference to Excel and then animate the output to the worksheet. Or you can create your own code using OLE DB provider to output to Excel. This latter will be the most coding but you can do away with those references and requirements for the users to have the application installed.

I have a sub that does the opposite, takes a Excel file and imports it into a dataset. I could code an Dataset to Excel export sub but it would take a little time.

Transfering Data To Excel Here is a link that gives some of the differing options available to programming for Excel from VB.net

 

[akomarek]

That's interesting, how a simple, single line of code in VBA is not available in a powerful language like VB.NET. Also, troubling on my end. I guess I had better get to work and try to figure out how I'm going to get this thing to work now.

Thank you for your insight and help, Tom, I appreciate it.

Al

 

[Tom]

If you need any help just let me know. Also make sure you check your private message box at the top of the screen, I sent ya something.

 

[JohnH]

That's interesting, how a simple, single line of code in VBA is not available in a powerful language like VB.NET.

You have same options for running the objects of the Office automation library from VB.Net as from VBA, as Tom also said. DoCmd is a object exposed by the Access Application object. See internet for example Automation of Access Reports - Xtreme Visual Basic Talk

When posting put code in code boxes to make it readable.

 

[James_Nguyen_Anewtech]

export to excel vb2005

You can consider excel as database and update data to databse.

Private Sub export_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles export.Click
Dim s As String = ""
Dim i As Integer = 0
Me.TopMost = False
Try
With exl
SaveFileDialog1.ShowDialog()
If Windows.Forms.DialogResult.OK Then
s = SaveFileDialog1.FileName
exl.Visible = False
.Workbooks.Add()
.Range("A1").Value = "DESCRIPTION"
.Range("B1").Value = "PNMODEL"
.Range("C1").Value = "INSTOCK_DATE"
.Range("D1").Value = "QTY"
exl.ActiveWorkbook.Close(True, s)
Me.TopMost = True
End If
End With
Catch ex As Exception
MsgBox(ex.ToString)
Exit Sub
End Try
Me.Refresh()
export(s)
End Sub

Private Sub export(ByVal ss As String)
Dim s1 As String = ss
Dim conn As New OleDbConnection
Dim cmd As New OleDbCommand
Dim connString As String = "Data Source=" & _
s1 & ";" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=Excel 8.0;"
conn.ConnectionString = connString
Dim strsql As String = " INSERT INTO [sheet1$](description, pnmodel, instock_date, qty) VALUES ("
Dim introw As Integer = 0
Dim temp As String = ""
Dim cellno As Integer = DataGridView2.Columns.Count
Dim intcol As Integer = 0
Try
For introw = 0 To DataGridView2.Rows.Count - 1
For intcol = 0 To cellno - 1
temp = DataGridView2.Rows(introw).Cells(intcol).Value.ToString
If intcol = DataGridView2.Columns.Count - 1 Then
strsql = strsql & " " & "'" & temp & "'" & ")"
Else
strsql = strsql & " " & "'" & temp & "'" & ","
End If
Next
Try
Dim cmdgrid As OleDbCommand = New OleDbCommand(strsql, conn)
conn.Open()
Dim sdrgrid As OleDbDataReader = cmdgrid.ExecuteReader
Catch exc As Exception
MsgBox(exc.Message)
Exit Sub
Finally
conn.Close()
End Try
strsql = " INSERT INTO [sheet1$](description, pnmodel, instock_date, qty) VALUES ("
Next
Catch ex As Exception
Exit Sub
End Try
End Sub