[Excel,VB2010 Automation] Excel keywords not defined in VB2010

[Tikk]

Hi,

Before I say anything further I have already added a reference to Microsoft Excel 11.0 (I am using Excel 2003) and have added the line Imports Microsoft.Office.Interop.Excel.

I am using VB2010 to copy data from a csv file into a Excel workbook which contains a macro that performs a number of tasks. The output data is then copied into a new worksheet and saved. Excel is then closed.

I am using VB2010 to copy the data from the first workbook into the workbook containing the macro. It appears when I use certain Excel keywords such as xlToLeft and Excel keywords which are within parenthesis I get an error saying the variable is not defined or may not be accessible due to its protection level. Why, if I have imported the Excel object library, are these keywords not being picked up by VB2010?

Thanks,
Tikk

Imports Microsoft.Office.Interop.Excel

Public Class Form1
    Dim strMacroBook As String
    Dim xlapp As Object
    Dim xlworkbook1 As Excel.Workbook
    Dim xlSheet1 As Excel.Worksheet
    Dim strFileName As String
    Dim strName1, strName2 As String



    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTestData.Click



        If OpenFileDialog1.ShowDialog = DialogResult.OK Then
            strFileName = OpenFileDialog1.FileName

        End If

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnQuit.Click
        End

    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddTS.Click
        Try
            Dim MacroBook As String
            Dim First_Row As Long, Last_Row As Long
            Dim First_Col As Integer, Last_Col As Integer


            xlapp = CreateObject("Excel.Application")
            xlworkbook1 = xlapp.workbooks.open(strFileName)
            strName1 = xlapp.activesheet.name


            Dim First_Row, Last_Row As Long
            Dim First_Col, Last_Col As Integer
            Dim xlsheet1 = xlapp.ActiveBook.Worksheets(strName1)
            'Check there is at least one non-empty cell
            If WorksheetFunction.CountA(Cells) > 0 Then
                'Determine actual used range of worksheet
                First_Row = xlsheet1.Cells.Find(What:="*", After:=Cells(Rows.Count, Columns.Count), _
                    SearchDirection:=xlNext, SearchOrder:=xlByRows).Row
                First_Col = xlsheet1.Cells.Find("*", After:=Cells(Rows.Count, Columns.Count), _
                    SearchDirection:=xlNext, SearchOrder:=xlByColumns).Column
                Last_Row = xlsheet1.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
                Last_Col = xlsheet1.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
            End If

            xlapp.visible = True
            xlapp.activeworkbook.close(False)
            strMacroBook = "C:\TimeStamp_10Hz\MacroBook.xls"
            System.Diagnostics.Process.Start(strMacroBook)
            xlapp.visible = True
            MacroBook = xlapp.ActiveWorkbook.Name
            strName2 = xlapp.activesheet.name
            xlapp.sheets(strName2).Range("A1").PasteSpecial()
            xlapp.run("TimeStamp_10Hz")
            xlapp.workbooks(MacroBook).close(False)
            xlapp.quit()
            xlapp = Nothing
        Catch
            MsgBox("Please reload test data")
            xlapp.quit()
        End Try

    End Sub
End Class

[vbfbryce]

Are you getting the error(s) in VB2010 or in the Excel macro?

If in Excel, can you run it standalone (not thru VB) successfully?

[westconn1]

It appears when I use certain Excel keywords such as xlToLeft

try xlapp.xltoleft

[Tikk]

Sorry for the delayed reply. I needed it to run off vb. I found that in order to use certain keywords such as xlprevious I needed to define them fully, e.g to use xlprevious I needed to say SearchDirection:=xlSearchDirection.xlPrevious.

[Siddharth Rout]

Yes, you have to define them fully or you can also use their constant values for example

SearchDirection:=xlSearchDirection.xlPrevious

can also be written as

SearchDirection:=2

Whenever in doubt, simply type it in the Immediate window in Excel and you will get the values

?xlNext
1
?xlPrevious
2

If you are into automating Excel from vb.net then you might want to have a look at this link.

TOPIC: VB.Net and Excel

Link: http://siddharthrout.wordpress.com/vb-net-and-excel/

HTH