Guten Morgen Said,
Du hast eine Referenz auf das Workbook, aber keine auf ein Worksheet.
Versuch mal folgendes:
Sub LoopThroughFiles()
Dim wbk As Workbook
RootFolder = MacScript("return (path to desktop folder) as String")
If Val(Application.Version) < 15 Then
scriptstr = "(choose folder with prompt ""Select the folder""" & _
" default location alias """ & RootFolder & """) as string"
Else
scriptstr = "return posix path of (choose folder with prompt ""Select the folder""" & _
" default location alias """ & RootFolder & """) as string"
End If
folderPath = MacScript(scriptstr)
On Error GoTo 0
MyFolder = folderPath
MyFile = Dir(MyFolder)
Do While MyFile <> ""
Set wbk = Workbooks.Open(Filename:=MyFolder & MyFile, Password:="change")
Workbooks(MyFile).Activate
Rows("1:1").Select
Selection.RowHeight = 20
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFilter
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Font.Bold = False
wbk.Close savechanges:=True
MyFile = Dir
Loop
Application.ScreenUpdating = True
End Sub
Sub modLoopThroughFiles()
Dim wbk As Workbook
Dim wks As Worksheet
'
RootFolder = MacScript("return (path to desktop folder) as String")
If Val(Application.Version) < 15 Then
scriptstr = "(choose folder with prompt ""Select the folder""" & _
" default location alias """ & RootFolder & """) as string"
Else
scriptstr = "return posix path of (choose folder with prompt ""Select the folder""" & _
" default location alias """ & RootFolder & """) as string"
End If
folderPath = MacScript(scriptstr)
On Error GoTo 0
MyFolder = folderPath
MyFile = Dir(MyFolder)
Do While MyFile <> ""
Set wbk = Workbooks.Open(Filename:=MyFolder & MyFile, Password:="change")
Set wks = wbk.Worksheets(1)
With wks
'Workbooks(MyFile).Activate
.Rows("1:1").RowHeight = 20
.Range("A1").Select
.Range(Selection, Selection.End(xlToRight)).Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
.Range("A1").Select
.Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFilter
.Range("A2").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.Font.Bold = False
'
wbk.Close savechanges:=True
'
Set wks = Nothing
Set wkb = Nothing
'
MyFile = Dir
Loop
Application.ScreenUpdating = True
End Sub
By the way:
Selection, .Select, brauchst Du bei ~99% der Fälle nicht, da Du Deine Objekte kennst und mit Objektvarfiablen darauf zugreifen kannst.
Das ist ein anderes Thema; können wir aber gesondert nach der Lösung #1004 angehen; wenn Du magst. Ansonsten gibst genug Lektüre im Netz dazu.
|