VBA desparation!!!!

I have this piece of code that is ran from within Access. It opens a series of .xls files and does some formatting. My biggest problem right now is that I'm trying to hide a column and I keep getting the error message:

"Object doesn't support this property or method"

It happens on the following line of code:

Code:

---

                With myXLWrkSheet
                    .Select
                    .Rows("1:1").Font.Bold = True
                    .Columns("A").Insert
                    .Range("A2", .Range("B65536").end(xlUp).Offset(0, -1)).FormulaR1C1 _
                    = "=HYPERLINK(RC[14],RC[1])"
                    .Selection.EntireColumn.Hidden = True  ////// Errors out here \\\\\\\
                    .Columns("A:V").AutoFit
                End With

---

Here is the whole function below:

Code:

---

Public Function formatXLFiles()
    Dim fso                As New FileSystemObject
    Dim myXLApp            As Object
    Dim myXLWrkBook        As Object
    Dim myXLWrkSheet        As Object
    Dim filepath            As String
    Dim f                  As Folder
    Dim fj                  As Files
    Dim f2                  As File
    Dim fc                  As Folders
    Dim aHit                As Integer
    Dim Selection          As Variant
    Dim numRows, numCols    As Integer
   
    Const xlToRight = -4161
    Const xlUnderLineStyleNone = -4142
    Const xlAutomatic = -4105
    Const xlDown = -4121
    Const xlUp = -4162
    Const xlToLeft = -4159
   
    filepath = "C:\qatools\FieldReports"
   
    If fso.FolderExists(filepath) Then
        Set f = fso.GetFolder(filepath)
        Set fc = f.SubFolders
        Set fj = f.Files

        For Each f2 In fj
            Set myXLApp = CreateObject("Excel.Application")
            Set myXLWrkBook = myXLApp.Workbooks.Open(f2)
           
            Set myXLWrkSheet = myXLWrkBook.Worksheets(1)
            myXLWrkBook.Application.Windows(1).Visible = True
           
            With myXLWrkSheet
                With .Rows("1:1").Font
                    .Name = "MS Sans Serif"
                    .Bold = True
                    .Size = 8.5
                End With
                .Columns("A:V").AutoFit
            End With
           
            If f2 Like "*DETAIL*" Then
                With myXLWrkSheet
                    .Select
                    .Rows("1:1").Font.Bold = True
                    .Columns("A").Insert
                    .Range("A2", .Range("B65536").end(xlUp).Offset(0, -1)).FormulaR1C1 _
                    = "=HYPERLINK(RC[14],RC[1])"
                    .Selection.EntireColumn.Hidden = True
                    .Columns("A:V").AutoFit
                End With
            End If
            myXLWrkBook.Close True
            Set myXLWrkSheet = Nothing
            Set myXLWrkBook = Nothing
        Next
    End If
   
    MsgBox "Processing Complete!"
    End
End Function

---

I'm really getting desparate with this...PLEASE SOMEONE HELP!!!!!

Thanks,
Blake

Any ideas anyone?

which column are you trying to hide, i don't see a specific column selected

I'm actually trying to hide 2 columns; column 2 and the last column (although I'm not sure how to reference the last column).

Any ideas?

as i don't use access i tried your code from vb, i got the same error when trying to hide the column, but i was able to use

.Columns("H").Hidden = True

see if this works for you

pete

Wes,

Unfortunately that didn't work. I'm new to VBA so with what I know and a quarter you might be able to buy a candy bar...hehe

I'm open to new suggestions though!!!!

Thanks,
Blake

Its the same issue that we solved yesterday. The .Selection object doesnt exist for the object.
Try just Selection.EntireColumn.Hidden = True without the "." before Selection.

Rob,

It didn't work. Gave me "Object Required" error.

Oops, sorry missed something.

after you add the hyperlink, you need to select the column so you can hide it using the Selection Object.

VB Code:

With myXLWrkSheet
        .Select
        .Rows("1:1").Font.Bold = True
        .Columns("A").Insert
        .Range("A2", .Range("B65536").End(xlUp).Offset(0, -1)).FormulaR1C1 _
         = "=HYPERLINK(RC[14],RC[1])"
        .Columns("A:A").Select
        Selection.EntireColumn.Hidden = True '  ////// Errors out here \\\\\\\
        .Columns("A:V").AutoFit
    End With

Rob,

It errors out on me with an "Object Required" error. It happens on the

Selection.EntireColumn.Hidden = True

line of code.

Code:

---

    With myXLWrkSheet
        .Select
        .Rows("1:1").Font.Bold = True
        .Columns("A").Insert
        .Range("A2", .Range("B65536").End(xlUp).Offset(0, -1)).FormulaR1C1 _
        = "=HYPERLINK(RC[14],RC[1])"
        .Columns("A:A").Select
        Selection.EntireColumn.Hidden = True '  ////// Errors out here \\\\\\\
        .Columns("A:V").AutoFit
    End With

---

Thats weird because it ran fine for me on a test workbook.
Try it in a new blank workbook.

Rob,

Are you running this code from within an Access DB? That's how I am running this app...from within Access.

Here is my test from within an Access module.

VB Code:

Private Sub Test8()
    Dim oApp As Excel.Application
    Dim oWB As Excel.Workbook
    
    Set oApp = GetObject(, "Excel.Application")
    Set oWB = oApp.Workbooks(1)
    
    Dim myXLWrkSheet As Excel.Worksheet
    Set myXLWrkSheet = oWB.Sheets(1)
    With myXLWrkSheet
        .Select
        .Rows("1:1").Font.Bold = True
        .Columns("A").Insert
        .Range("A2", .Range("B65536").End(xlUp).Offset(0, -1)).FormulaR1C1 _
         = "=HYPERLINK(RC[14],RC[1])"
        .Columns("A:A").Select
        .Columns("A:A").EntireColumn.Hidden = True 
        .Columns("A:V").AutoFit
    End With
End Sub

This was still part of the previous issue with the Selection object. Access has one and Excel does not.

Let me give this a shot Rob...

Thanks!!!

Sorry Rob,

It didn't fly either.

Blake

I dont know what to tell you. I recreated the enviroment and got it to work. What was the error, if any?

There was no error anymore....it just didn't hide the columns.

Step through the code (Press F8). It may be a speed issue.

Hold your breath brother.....I think it just worked. I'm running another test right now.

Rob,

You da man brother. It finally worked. I don't know why it didn't work before but for some reason it's flyin' now. Thanks so much for your help!!!!

Using your .Columns object...can I set the Autofilter for columns as well and if so, what is the syntax for that?

Thanks again,

Blake

False alarm Rob.

It don't work!

Did it just not hide the column or error? During run more or stepping through (F8)?

Here is an example from the help file on the AutoFiler object. I havent used it before so dont know if I can help you that much on it.,

VB Code:

Set w = Worksheets("Crew")
w.AutoFilterMode = False
For col = 1 To UBound(filterArray(), 1)
    If Not IsEmpty(filterArray(col, 1)) Then
        If filterArray(col, 2) Then
            w.Range(currentFiltRange).AutoFilter field:=col, Criteria1:=filterArray(col, 1), _
                    Operator:=filterArray(col, 2), Criteria2:=filterArray(col, 3)
        Else
            w.Range(currentFiltRange).AutoFilter field:=col, Criteria1:=filterArray(col, 1)
        End If
    End If
Next

Hey Rob,

Just wanted to let you know....your code did work. For some reason when I made the changes to my code they didn't take and it reverted back to the old code. I caught it and replaced it with your code and it works great. I will try the Filter code tomorrow.

Thanks for all you help bro,

Blake