Results 1 to 2 of 2

Thread: Export two excel tabs in VB.NET fails

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    3

    Export two excel tabs in VB.NET fails

    All

    I have a MS Access 2010 database which I am converting into VB.NET 2010.

    In the original VBA-code I have next sub which selects two tabs from a given Excel-file (which is already opened in a previous step) and copies them to a new file. This VBA-code works as expected. However, I now copy/pasted this code into VB.NET 2010 and this results in errors.

    In VBA,the sub is defined as Private Sub Export(ByVal strFilename As String, ByVal oXL As Excel.Application, Optional ByVal strWorksheet As String). VB.NET fails over ByVal oXL As Excel.Application although I did enable reference Microsoft Excel 14.0 Object Library. So to avoid the error I defined it as an object but I now get errors as VB.NET handles the ARRAY as the VB reserved function instead of the XLS reserved function. The code can't be compiled due to error message "'Array' is a type and cannot be used as an expression".

    Someone an idea how to solve this?


    Code:
        Private Sub Export(ByVal strFilename As String, ByVal oXL As Object, Optional ByVal strWorksheet As String = "")
            Const xlWebArchive = 45
            If strFilename <> "Techn" Then
                oXL.Sheets(strWorksheet).Copy()
            Else
                oXL.Sheets(Array("Sheet1", "Sheet2")).Copy()
            End If
            oXL.DisplayAlerts = False
            oXL.ActiveWorkbook.saveas(FileName:=strRoot & strFilename)
            oXL.ActiveWorkbook.saveas(FileName:=strRoot & strFilename, FileFormat:=xlWebArchive, CreateBackup:=False)
            oXL.DisplayAlerts = True
            oXL.ActiveWorkbook.Close()
        End Sub
    Last edited by ino_mart; Feb 2nd, 2015 at 08:07 AM.

  2. #2
    Frenzied Member
    Join Date
    Oct 2012
    Location
    Tampa, FL
    Posts
    1,187

    Re: Export two excel tabs in VB.NET fails

    Quote Originally Posted by ino_mart View Post
    All

    I have a MS Access 2010 database which I am converting into VB.NET 2010.

    In the original VBA-code I have next sub which selects two tabs from a given Excel-file (which is already opened in a previous step) and copies them to a new file. This VBA-code works as expected. However, I now copy/pasted this code into VB.NET 2010 and this results in errors.

    In VBA,the sub is defined as Private Sub Export(ByVal strFilename As String, ByVal oXL As Excel.Application, Optional ByVal strWorksheet As String). VB.NET fails over ByVal oXL As Excel.Application although I did enable reference Microsoft Excel 14.0 Object Library. So to avoid the error I defined it as an object but I now get errors as VB.NET handles the ARRAY as the VB reserved function instead of the XLS reserved function. The code can't be compiled due to error message "'Array' is a type and cannot be used as an expression".

    Someone an idea how to solve this?


    Code:
        Private Sub Export(ByVal strFilename As String, ByVal oXL As Object, Optional ByVal strWorksheet As String = "")
            Const xlWebArchive = 45
            If strFilename <> "Techn" Then
                oXL.Sheets(strWorksheet).Copy()
            Else
                oXL.Sheets(Array("Sheet1", "Sheet2")).Copy()
            End If
            oXL.DisplayAlerts = False
            oXL.ActiveWorkbook.saveas(FileName:=strRoot & strFilename)
            oXL.ActiveWorkbook.saveas(FileName:=strRoot & strFilename, FileFormat:=xlWebArchive, CreateBackup:=False)
            oXL.DisplayAlerts = True
            oXL.ActiveWorkbook.Close()
        End Sub
    I think you have done the import steps, but lets confirm:

    From the VS menu, choose Project > Add Reference > .NET Tab > Microsoft.Office.Interop.Excel > "OK"

    Now you have to declare the import in your class. Go to your form code. Above your form class designation, put:

    Code:
    Imports Microsoft.Office.Interop
    Now declare your sub:

    Code:
        Private Sub Export(ByVal strFilename As String, ByVal oXL As Excel.Application, Optional ByVal strWorksheet As String = "")
            Const xlWebArchive = 45
            If strFilename <> "Techn" Then
                oXL.Sheets(strWorksheet).Copy()
            Else
                oXL.Sheets({"Sheet1", "Sheet2"}).Copy()
            End If
            oXL.DisplayAlerts = False
            oXL.ActiveWorkbook.SaveAs(Filename:=strRoot & strFilename)
            oXL.ActiveWorkbook.saveas(FileName:=strRoot & strFilename, FileFormat:=xlWebArchive, CreateBackup:=False)
            oXL.DisplayAlerts = True
            oXL.ActiveWorkbook.Close()
        End Sub
    Everything looks fine on my end. Let me know if you have any issues.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width