I have the following questions to ask you about the excel macro recording,
VBA, and Visual basic.net

1. I am not certain of when I can use a recording macro in excel? I
basically need to have the visual basic.net 2005 desktop application control
the processing since it is the one the drives the generation of separate
excel worksheets in distinct workbooks. (There is one worksheet per workbook
since each spreadsheet is email and to differet customers.)
2. When I use the recroding macro, when it be before I create the excel
object, while I am putting data into the excel object, or after the excel
worksheet has been generated?
3. I am not certain where you are refering to using the VBA code. Am I
including the VBA code in the following .NET code:
xlApp = New Excel.Application
xlClApp = New Excel.ApplicationClass
xlApp.Workbooks.Add()
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = CType(xlWorkBook.Sheets("sheet1"),
Microsoft.Office.Interop.Excel.Worksheet)
xlApp.Visible = False
xlApp.ScreenUpdating = True
dt = ds4.Tables(0)
'Add the column headings for the from the dataset
Dim dc As DataColumn
Dim iCols As Int32 = 0
For Each dc In dt.Columns
xlWorkSheet.Range("A1").Offset(0, iCols).Value = dc.ColumnName
xlWorkSheet.Range("A1").Offset(0, iCols).Font.Bold = True
xlWorkSheet.Range("A1").Offset(0, iCols).BorderAround()
iCols += 1
Next

Dim iRows As Int32
For iRows = 0 To dt.Rows.Count - 1
xlWorkSheet.Range("A2").Offset(0, iCols).Select()
xlWorkSheet.Range("A2").Offset(0, iCols).Justify()
xlWorkSheet.Range("A2").Offset(0, iCols).WrapText = True
xlWorkSheet.Range("A2").Offset(iRows).Resize(1, iCols).Value = _
dt.Rows(iRows).ItemArray()
xlWorkSheet.Range("A2").Offset(0, iCols).BorderAround()
Next
excelFile = "c:\exceltst.xls"
xlWorkBook.Sheets("sheet1").SaveAs(excelFile)
4. How can I use VBA code here?
5. Here is my code so far:
Private Sub ShowReport_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs)
Handles Button1.Click
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim xlClApp As Excel.ApplicationClass
Dim excelFile As String = " "
Dim dt As DataTable
Dim da4 As SqlDataAdapter
cnn4 = New SqlConnection(connectionString)
cmd4 = New SqlCommand("stored procedure name", cnn4)
cmd4.CommandType = CommandType.StoredProcedure
With cmd4
..Parameters.Add("@parm1", SqlDbType.VarChar).Value = strParm1
..Parameters.Add("@parm2", SqlDbType.VarChar).Value = strParm2
End With
da4 = New SqlDataAdapter(cmd4)
da4.Fill(ds4)
xlApp = New Excel.Application
xlClApp = New Excel.ApplicationClass
xlApp.Workbooks.Add()
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = CType(xlWorkBook.Sheets("sheet1"),
Microsoft.Office.Interop.Excel.Worksheet)
xlApp.Visible = False
xlApp.ScreenUpdating = True
dt = ds4.Tables(0)
'Add the column headings for the from the dataset
Dim dc As DataColumn
Dim iCols As Int32 = 0
For Each dc In dt.Columns
xlWorkSheet.Range("A1").Offset(0, iCols).Value = dc.ColumnName
xlWorkSheet.Range("A1").Offset(0, iCols).Font.Bold = True
xlWorkSheet.Range("A1").Offset(0, iCols).BorderAround()
iCols += 1
Next

Dim iRows As Int32
For iRows = 0 To dt.Rows.Count - 1
xlWorkSheet.Range("A2").Offset(0, iCols).Select()
xlWorkSheet.Range("A2").Offset(0, iCols).Justify()
xlWorkSheet.Range("A2").Offset(0, iCols).WrapText = True
xlWorkSheet.Range("A2").Offset(iRows).Resize(1, iCols).Value = _
dt.Rows(iRows).ItemArray()
xlWorkSheet.Range("A2").Offset(0, iCols).BorderAround()
Next
excelFile = "c:\exceltst.xls"
xlWorkBook.Sheets("sheet1").SaveAs(excelFile)
xlWorkBook.Close()
xlApp.Quit()
xlWorkSheet = Nothing
xlWorkBook = Nothing
xlClApp = Nothing
da4.Dispose()
cmd4.Dispose()
cnn4.Close()


Thanks!

1. Recording a macro does not have anything to do with integration with .NET.
You tell Excel to start recording, perform actions as you normally do, and
then tell Excel when you're done. Behind the scenes, Excel uses VBA to record
your instructions. That is easiest method to know what code we need to write
in .NET to integrate.

2. Recording a macro does not have anything to do with integration with .NET.

3,4,5. Please refer these sites..Hope this helps

http://support.microsoft.com/kb/301982
http://www.c-sharpcorner.com/UploadF...ortsInNet.aspx

Sorry, i was too busy these days to go through your other posts..Will
definitely go through..when time permits. Since no one has responded to this
post I thought of replying.....

If this post helps click Yes
---------------
Jacob Skaria


"douglas" wrote:

I have the following questions to ask you about the excel macro recording,
VBA, and Visual basic.net

1. I am not certain of when I can use a recording macro in excel? I
basically need to have the visual basic.net 2005 desktop application control
the processing since it is the one the drives the generation of separate
excel worksheets in distinct workbooks. (There is one worksheet per workbook
since each spreadsheet is email and to differet customers.)
2. When I use the recroding macro, when it be before I create the excel
object, while I am putting data into the excel object, or after the excel
worksheet has been generated?
3. I am not certain where you are refering to using the VBA code. Am I
including the VBA code in the following .NET code:
xlApp = New Excel.Application
xlClApp = New Excel.ApplicationClass
xlApp.Workbooks.Add()
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = CType(xlWorkBook.Sheets("sheet1"),
Microsoft.Office.Interop.Excel.Worksheet)
xlApp.Visible = False
xlApp.ScreenUpdating = True
dt = ds4.Tables(0)
'Add the column headings for the from the dataset
Dim dc As DataColumn
Dim iCols As Int32 = 0
For Each dc In dt.Columns
xlWorkSheet.Range("A1").Offset(0, iCols).Value = dc.ColumnName
xlWorkSheet.Range("A1").Offset(0, iCols).Font.Bold = True
xlWorkSheet.Range("A1").Offset(0, iCols).BorderAround()
iCols += 1
Next

Dim iRows As Int32
For iRows = 0 To dt.Rows.Count - 1
xlWorkSheet.Range("A2").Offset(0, iCols).Select()
xlWorkSheet.Range("A2").Offset(0, iCols).Justify()
xlWorkSheet.Range("A2").Offset(0, iCols).WrapText = True
xlWorkSheet.Range("A2").Offset(iRows).Resize(1, iCols).Value = _
dt.Rows(iRows).ItemArray()
xlWorkSheet.Range("A2").Offset(0, iCols).BorderAround()
Next
excelFile = "c:\exceltst.xls"
xlWorkBook.Sheets("sheet1").SaveAs(excelFile)
4. How can I use VBA code here?
5. Here is my code so far:
Private Sub ShowReport_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs)
Handles Button1.Click
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim xlClApp As Excel.ApplicationClass
Dim excelFile As String = " "
Dim dt As DataTable
Dim da4 As SqlDataAdapter
cnn4 = New SqlConnection(connectionString)
cmd4 = New SqlCommand("stored procedure name", cnn4)
cmd4.CommandType = CommandType.StoredProcedure
With cmd4
.Parameters.Add("@parm1", SqlDbType.VarChar).Value = strParm1
.Parameters.Add("@parm2", SqlDbType.VarChar).Value = strParm2
End With
da4 = New SqlDataAdapter(cmd4)
da4.Fill(ds4)
xlApp = New Excel.Application
xlClApp = New Excel.ApplicationClass
xlApp.Workbooks.Add()
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = CType(xlWorkBook.Sheets("sheet1"),
Microsoft.Office.Interop.Excel.Worksheet)
xlApp.Visible = False
xlApp.ScreenUpdating = True
dt = ds4.Tables(0)
'Add the column headings for the from the dataset
Dim dc As DataColumn
Dim iCols As Int32 = 0
For Each dc In dt.Columns
xlWorkSheet.Range("A1").Offset(0, iCols).Value = dc.ColumnName
xlWorkSheet.Range("A1").Offset(0, iCols).Font.Bold = True
xlWorkSheet.Range("A1").Offset(0, iCols).BorderAround()
iCols += 1
Next

Dim iRows As Int32
For iRows = 0 To dt.Rows.Count - 1
xlWorkSheet.Range("A2").Offset(0, iCols).Select()
xlWorkSheet.Range("A2").Offset(0, iCols).Justify()
xlWorkSheet.Range("A2").Offset(0, iCols).WrapText = True
xlWorkSheet.Range("A2").Offset(iRows).Resize(1, iCols).Value = _
dt.Rows(iRows).ItemArray()
xlWorkSheet.Range("A2").Offset(0, iCols).BorderAround()
Next
excelFile = "c:\exceltst.xls"
xlWorkBook.Sheets("sheet1").SaveAs(excelFile)
xlWorkBook.Close()
xlApp.Quit()
xlWorkSheet = Nothing
xlWorkBook = Nothing
xlClApp = Nothing
da4.Dispose()
cmd4.Dispose()
cnn4.Close()


Thanks!