VBA desparation!!!!

[blakemckenna]

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:

                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:

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

[blakemckenna]

Any ideas anyone?

[john24]

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

[blakemckenna]

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?

[westconn1]

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

[blakemckenna]

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

[RobDog888]

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.

[blakemckenna]

Rob,

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

[RobDog888]

Oops, sorry missed something.

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

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

[blakemckenna]

Rob,

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

Selection.EntireColumn.Hidden = True

line of 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

[RobDog888]

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

[blakemckenna]

Rob,

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

[RobDog888]

Here is my test from within an Access module.

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.

[blakemckenna]

Let me give this a shot Rob...

Thanks!!!

[blakemckenna]

Sorry Rob,

It didn't fly either.

Blake

[RobDog888]

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

[blakemckenna]

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

[RobDog888]

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

[blakemckenna]

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

[blakemckenna]

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

[blakemckenna]

False alarm Rob.

It don't work!

[RobDog888]

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.,

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

[blakemckenna]

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