none
Unable to read .xlsx file into access 2007 RRS feed

  • Question

  • Dim xlApp As Excel.Application
    Dim introw As Integer
    Dim intcol As Integer
    Dim intFldCntr As Integer
    Dim lastcol, lastrow As Integer
    dim xlsname as string

    XLSName= "C:\mydata\PL.xlsx"

    With xlApp
                    .Workbooks.Open FileName:=XLSName
                    .Sheets(1).Select
                    lastrow = .ActiveSheet.UsedRange.Rows.Count
                    lastcol = .ActiveSheet.UsedRange.Columns.Count
    End With

    The above code works fine for .xls file but not for .xlsx file. Please guide me.

    Thanks,

    Chirag Patel


    ckp

    Monday, January 28, 2013 7:08 AM

All replies

  • Are you getting an error? Perhaps you could describe what is not working.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, January 28, 2013 2:43 PM
  • Hello,

    The following requires a OpenFileDialog, you select a file to get used rows and columns. As coded KSG_USE is defined under project settings, compile tab, Advanced compiler options, customer constants add KSG_USE. The path for initialpath will not be used but instead your My Documents folder. The sheet is hard coded to Sheet1$.

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim SelectStatement As String = ""
    		' Not used if not exists
            Dim InitialPath As String = "C:\DotnetLand2010\Excel\GetUsedRowsAndColumns\bin\Debug"
            OpenFileDialog1.Title = "Please select a file to open"
            OpenFileDialog1.FileName = ""
            If IO.Directory.Exists(InitialPath) Then
                OpenFileDialog1.InitialDirectory = InitialPath
            Else
                OpenFileDialog1.InitialDirectory = _
                    System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
            End If
            OpenFileDialog1.Filter = "Excel 2007 (*.xlsx)|*.xlsx|Excel pre 2007|*.xls"
            If OpenFileDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then
                Dim xlApp As Excel.Application = Nothing
                Dim xlWorkBooks As Excel.Workbooks = Nothing
                Dim xlWorkBook As Excel.Workbook = Nothing
                Dim xlWorkSheet As Excel.Worksheet = Nothing
                xlApp = New Excel.Application
                xlApp.DisplayAlerts = False
                xlWorkBooks = xlApp.Workbooks
                xlWorkBook = xlWorkBooks.Open(OpenFileDialog1.FileName)
                xlApp.Visible = False
                xlWorkSheet = CType(xlWorkBook.Sheets("Sheet1"), Excel.Worksheet)
                '
                ' To ensure proper object disposal I have used three range
                ' variables.
                '
                Dim UsedRange = xlWorkSheet.UsedRange
                Dim RowRange = UsedRange.Rows
                Dim ColRange = UsedRange.Columns
                SelectStatement = String.Format("{0}$A1:{1}{2}", xlWorkSheet.Name, ColRange.Count.ExcelColumnName, RowRange.Count)
                Console.WriteLine("Sheet: {0} Rows used {1} Columns used {2} [{3}]",
                                  xlWorkSheet.Name,
                                  RowRange.Count,
                                  ColRange.Count,
                                  String.Format("{0}$A1:{1}{2}", xlWorkSheet.Name, ColRange.Count.ExcelColumnName, RowRange.Count)
                                  )
                xlWorkBook.Close()
                xlApp.UserControl = True
                xlApp.Quit()
                '
                ' Objects are released last to first
                '
                If Not ColRange Is Nothing Then
                    Marshal.FinalReleaseComObject(ColRange)
                    ColRange = Nothing
                End If
                If Not RowRange Is Nothing Then
                    Marshal.FinalReleaseComObject(RowRange)
                    RowRange = Nothing
                End If
                If Not UsedRange Is Nothing Then
                    Marshal.FinalReleaseComObject(UsedRange)
                    UsedRange = Nothing
                End If
                If Not xlWorkSheet Is Nothing Then
                    Marshal.FinalReleaseComObject(xlWorkSheet)
                    xlWorkSheet = Nothing
                End If
                If Not xlWorkBook Is Nothing Then
                    Marshal.FinalReleaseComObject(xlWorkBook)
                    xlWorkBook = Nothing
                End If
                If Not xlWorkBooks Is Nothing Then
                    Marshal.FinalReleaseComObject(xlWorkBooks)
                    xlWorkBooks = Nothing
                End If
                If Not xlApp Is Nothing Then
                    Marshal.FinalReleaseComObject(xlApp)
                    xlApp = Nothing
                End If
    #If KSG_USE Then
                GC.Collect()
                GC.WaitForPendingFinalizers()
                GC.Collect()
                GC.WaitForPendingFinalizers()
    #End If
            End If
        End Sub


    kevininstructor

    Wednesday, January 30, 2013 6:33 PM