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:
Here is the whole function below: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
I'm really getting desparate with this...PLEASE SOMEONE HELP!!!!!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
Thanks,
Blake
