CODEWITHSUNDEEP
excelms-access
RyszardJ
RyszardJ

Reputation: 39

Access vba excel Range

I am using Access to loop through a data table and generate an Excel Workbook (with three sheets) for each row in the database table. All works very well until I introduce code using "Range" to hide some columns and rows. The code will work successfully on the first row but then fail. It will also fail if we run the code again. If we quit Access and then rerun the first row is again successful.

NewFileName = "C:\Paul2016Puzzle\TestNewName" + "Project" + Str(iteration)
            'MsgBox NewFileName

        Set XL = New Excel.Application
        Set WB = XL.Workbooks.Open(NewFileName)
        WB.Activate

        Set wks = WB.Worksheets(2)
        XL.ScreenUpdating = False
        XL.DisplayAlerts = False


        wks.Select
        WB.Sheets(2).Activate


        StrExcel = Chr(65 + WorkingColumns + 1)

         StrExcel = StrExcel + ":" + StrExcel
         MsgBox StrExcel

           WB.Sheets("Sheet 2").Select
           WB.Sheets("Sheet 2").Range(StrExcel).Select
           WB.Sheets("Sheet 2").Activate



                wks.Range(StrExcel).Activate
                wks.Columns(StrExcel).Select
                wks.Range(StrExcel).Select
                ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
                Selection.EntireColumn.Hidden = True

                Rows("12:12").Select
                ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
                Selection.EntireRow.Hidden = True




        wks.Cells(1, 1).ColumnWidth = 30 '(Set column width)
        For i = 2 To WorkingColumns + 1
        wks.Cells(1, i).ColumnWidth = 15
        Next i

Upvotes: 1

Views: 11668

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

Rather than trying to create the column letter, just reference the column number.
Chr(65 + WorkingColumns + 1) will fail - if WorkingColumns is 25 it will try and reference column [.

With reference to your comments. I use this procedure to find the last cell on a sheet:

' Purpose   : Finds the last cell containing data or a formula within the given worksheet.
'             If the Optional Col is passed it finds the last row for a specific column.
'---------------------------------------------------------------------------------------

    Public Function LastCell(wrkSht As Worksheet, Optional Col As Long = 0) As Range

        Dim lLastCol As Long, lLastRow As Long

        On Error Resume Next

        With wrkSht
            If Col = 0 Then
                lLastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
                lLastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
            Else
                lLastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
                lLastRow = .Columns(Col).Find("*", , , , xlByColumns, xlPrevious).Row
            End If

            If lLastCol = 0 Then lLastCol = 1
            If lLastRow = 0 Then lLastRow = 1

            Set LastCell = wrkSht.Cells(lLastRow, lLastCol)
        End With
        On Error GoTo 0

    End Function

You can then use it to find the last row/column containing data and hide everything after that:

Public Sub Main()

    Dim WB As Workbook
    Dim wks As Worksheet
    'Dim WorkingColumns As Long
    'Dim FirstRow As Long, LastRow As Long
    'Dim FirstCol As Long, LastCol As Long

    Set WB = ThisWorkbook
    Set wks = WB.Worksheets(2)

    'Not sure how you get the WorkingColumns figure,
    'so have set it to column 5 (column E).
    'WorkingColumns = 5

    'FirstCol = 2
    'LastCol = 8

    'FirstRow = 4
    'LastRow = 10

    'Find the last cell containing data.
    Dim rLastCell As Range
    Set rLastCell = LastCell(wks)

    With wks

        'This Offsets by 1 column, so looks at the column after the end of data.
        .Range(rLastCell.Offset(, 1), .Cells(1, Columns.Count)).EntireColumn.Hidden = True
        .Range(.Cells(13, 1), .Cells(Rows.Count, 1)).EntireRow.Hidden = True

        '''''''''''''''''''''''''Second Update'''''''''''''''''''''''
        'A range is written as Range(FirstCellRef, LastCellRef).
        'Cells references a single cell using row and column numbers (or letters).
        'You can use either .Cells(3, 1) or .Cells(3,"A") to reference cell A3.
        '.Range(.Cells(1, FirstCol), .Cells(1, LastCol)).EntireColumn.Hidden = True
        '.Range(.Cells(FirstRow, 1), .Cells(LastRow, 1)).EntireRow.Hidden = True

        'Set width of columns I:L
        '.Range(.Cells(1, 9), .Cells(1, 12)).ColumnWidth = 30

        'Set width of column N & P (column O is ignored).
        'Union(.Cells(1, 14), .Cells(1, 16)).ColumnWidth = 2
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

        '''''''''''''''''''''''''Original Code'''''''''''''''''''''''
        'Resize the number of columns to 8 wide, including column E.
        'So E:L.
        ' .Columns(WorkingColumns).Resize(, 8).Hidden = True
        ' .Rows(12).Hidden = True

        ' .Columns(1).ColumnWidth = 30

        'Resize Column 2 reference by +4.
        'So B:E
        ' .Columns(2).Resize(, WorkingColumns - 1).ColumnWidth = 15
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    End With

End Sub

Edit: I've updated the code to reference columns using first/last column and row numbers rather than the Resize method.

Upvotes: 2

Related Questions