Hyperlinking in Excel 2016

P

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
152
Hello! I have a VBA code which was built in 2006, worked in 2016, and broke in 2017 when we updated to Excel 2016. For the life of me, I cannot find the issue. This code is designed to link from excel file 1, copy information from excel file 2, and paste it to a worksheet in excel file 1 with some formatting. To be completely honest I am kinda desperate at this point. ANY assistance would be VASTLY appreciated...I should say the code runs 100% With no Syntax issue... However It does not actually populate the information.

sheet 1 is Invoices and Maps
sheet 2 is All Maintenance
sheet 3 is Maintenance Report




Code: 
[COLOR=#252C2F][FONT=Helvetica]Dim lst As New Collection[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Sub auto_open()[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Application.DisplayAlerts = False[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Application.ScreenUpdating = True[/FONT][/COLOR]

[COLOR=#252C2F][FONT=Helvetica]Response = MsgBox("Would you like to update list now? This will take some time.", vbYesNo)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]If Response = vbYes Then[/FONT][/COLOR]

[COLOR=#252C2F][FONT=Helvetica]MsgBox "WARNING! Close all other excel files before proceeding!", vbCritical[/FONT][/COLOR]

[COLOR=#252C2F][FONT=Helvetica]response2 = MsgBox("All other excel files closed and ready to update?", vbYesNo)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]If response2 = vbYes Then[/FONT][/COLOR]

[COLOR=#252C2F][FONT=Helvetica]Dim R As Integer, rd As Long, nd As Long, amm As Long, WkbkName As Object, wkst As Worksheet[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim sStartPath As String[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim sWhat As String[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim result As String[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim t As Integer[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim tmp As String[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim afa As Integer[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim Rw As Integer[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim Rt As Range[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim Rmt As Range[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim stDir As String[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim stFile As String[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim z As Range[/FONT][/COLOR]


[COLOR=#252C2F][FONT=Helvetica]'Clear workbook in preperation for update[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Worksheets(1).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]For Each wkst In Worksheets[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]If wkst.Name <> "Maintenance Report" And wkst.Name <> "Decayed Pole Top" Then[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]wkst.Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]wkst.Unprotect[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]wkst.UsedRange.Delete[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End If[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Next wkst[/FONT][/COLOR]

[COLOR=#252C2F][FONT=Helvetica]'Update Invoice Lists[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Worksheets(1).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]For Each wkst In Worksheets[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]If wkst.Name = "Invoices and Maps" Then[/FONT][/COLOR]



[COLOR=#252C2F][FONT=Helvetica]'2017 Invoice List and hyperlink[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]'Be sure to correct location. Include \ at end of location.[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]sStartPath = "E:\Sandbox\Test 1\Invoices" 'Where? Correct location. Enter address between quotation marks.[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]sWhat = "*.xlsx" 'What?[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]If lst.Count > 0 Then[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Do[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]lst.Remove lst.Count 'clears list if data already exists[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Loop Until lst.Count = 0[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End If[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]ThisWorkbook.Sheets(1).Columns(1).ClearContents[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]result = DigIn2(sStartPath, sWhat) 'First step[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]For t = lst.Count To 1 Step -1[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]ThisWorkbook.Sheets(1).Cells(t, 1) = lst(t) 'puts data in 1st sheet, 1st column[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]lst.Remove t[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Next t[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Rw = Range("A65536").End(xlUp).Row[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]afa = 1[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]For Each Cell In Range("A1:A" & Rw)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Range("A" & afa).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Hyperlinks.Add anchor:=Range("A" & afa), Address:=Range("A" & afa).Value[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]afa = afa + 1[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Next[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Columns("A:A").Sort Key1:=Range("A1"), order1:=xlAscending, Header:=xlNo, _[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom[/FONT][/COLOR]

[COLOR=#252C2F][FONT=Helvetica]'2017 Map List and Hyperlink[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Cells(1, 2).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Set z = ActiveCell[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]'Be sure to correct location. Do not include \ at end of location.[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]stDir = "E:\Sandbox\Test 1\Invoices\MAPS" 'Where? Correct location. Enter address between quotation marks. ""[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]stFile = Dir(stDir & "\*.pdf") 'What?[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Do Until stFile = ""[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]z.Hyperlinks.Add z, stDir & "" & stFile, , , stFile[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Set z = z.Offset(1)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]stFile = Dir()[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Loop[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Columns("B:B").Sort Key1:=Range("B1"), order1:=xlAscending, Header:=xlNo, _[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom[/FONT][/COLOR]


[COLOR=#252C2F][FONT=Helvetica]'Formating Invoice and Map Sheet[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Worksheets(1).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Range("A" & 1).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Selection.EntireRow.Insert[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]'Change to the proper year.[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]ActiveSheet.Range("A1") = "2017 Invoices"[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]ActiveSheet.Range("B1") = "2017 Maps"[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]ActiveWindow.DisplayGridlines = True[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Rows("1:1000").RowHeight = 20[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Columns("A:C").ColumnWidth = 20[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Range("A:C").Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].HorizontalAlignment = xlCenter[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].VerticalAlignment = xlCenter[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].WrapText = True[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]

[COLOR=#252C2F][FONT=Helvetica]Range("A" & 1).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End If[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Next wkst[/FONT][/COLOR]




[COLOR=#252C2F][FONT=Helvetica]'Update Maintenance[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]'Update 2017 Maintenance[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Worksheets(1).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]R = Range("A65536").End(xlUp).Row[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]For Each Cell In Range("A2:A" & R)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Cell.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=False[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Next Cell[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]CopyTargetBookmark = 1[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]For Each Workbook In Application.Workbooks[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]'Correct workbook name.[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]If Workbook.Name <> "2017 Maintenance Invoice Log.xlsm" Then[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Workbook.Activate[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Workbook.Worksheets(5).UsedRange.Copy[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]'Correct workbook name.[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Workbooks("2017 Maintenance Invoice Log.xlsm").Activate[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Worksheets(2).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Range("A" & CopyTargetBookmark).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]ActiveSheet.Paste[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]CopyTargetBookmark = CopyTargetBookmark + Workbook.Worksheets(5).UsedRange.Rows.Count[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End If[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Next Workbook[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]For Each WkbkName In Application.Workbooks()[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]If WkbkName.Name <> ThisWorkbook.Name Then WkbkName.Close[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Next[/FONT][/COLOR]

[COLOR=#252C2F][FONT=Helvetica]'Formatting[/FONT][/COLOR]

[COLOR=#252C2F][FONT=Helvetica]'Yearly Sheets[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]For Each Worksheet In Worksheets[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]If Worksheet.Name <> "Invoices and Maps" And Worksheet.Name <> "Maintenance Report" Then[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Worksheet.Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]rd = ActiveSheet.UsedRange.Rows.Count[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Range("A2", "D" & rd).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Selection.Sort Key1:=Range("A2"), order1:=xlAscending, Header:=xlNo, key2:=Range("B2")[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]nd = 1[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Do While Cells(nd, 2).Value = Cells(1 + nd, 2).Value[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]If ActiveCell.Value <> "" Then[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Rows(nd).EntireRow.Delete[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Else[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Exit Do[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End If[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Loop[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Rows("1:1").RowHeight = 30[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Columns("A:A").ColumnWidth = 12[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Columns("B:B").ColumnWidth = 12[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Columns("C:C").ColumnWidth = 12[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Columns("D:D").ColumnWidth = 80[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]amm = ActiveSheet.UsedRange.Rows.Count[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Range("A1", "D" & amm).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].HorizontalAlignment = xlCenter[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].VerticalAlignment = xlCenter[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].WrapText = True[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection.Borders(xlEdgeLeft)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].ColorIndex = 0[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Weight = xlMedium[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection.Borders(xlEdgeTop)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].ColorIndex = 0[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Weight = xlMedium[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection.Borders(xlEdgeBottom)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].ColorIndex = 0[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Weight = xlMedium[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection.Borders(xlEdgeRight)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].ColorIndex = 0[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Weight = xlMedium[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection.Borders(xlEdgeLeft)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].ColorIndex = 0[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Weight = xlThin[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection.Borders(xlEdgeTop)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].ColorIndex = 0[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Weight = xlThin[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection.Borders(xlEdgeBottom)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].ColorIndex = 0[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Weight = xlThin[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection.Borders(xlEdgeRight)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].ColorIndex = 0[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Weight = xlThin[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection.Borders(xlInsideVertical)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].ColorIndex = 0[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Weight = xlThin[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection.Borders(xlInsideHorizontal)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Weight = xlThin[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With ActiveSheet[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].AutoFilterMode = False[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Range("A1:D1").AutoFilter[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Range("A1", "D1").Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Font.Bold = True[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Interior.Color = vbYellow[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Range("A" & 1).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End If[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Next Worksheet[/FONT][/COLOR]

[COLOR=#252C2F][FONT=Helvetica]'Ending[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Worksheets(1).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]ActiveSheet.Range("B1").Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Columns("A").Hidden = True[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Cells.Replace What:=".pdf", Replacement:="", LookAt:=xlPart, _[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]SearchOrder:=xlByRows, MatchCase:=False[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]ActiveSheet.EnableSelection = xllockedCells[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Worksheets(1).Protect[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]MsgBox ("Update Successful!")[/FONT][/COLOR]

[COLOR=#252C2F][FONT=Helvetica]Else[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]MsgBox "Update Aborted! Press 'Ctrl+Shift+Q' to restart the update."[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Exit Sub[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End If[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Else[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Exit Sub[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End If[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Application.DisplayAlerts = True[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Application.ScreenUpdating = True[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End Sub[/FONT][/COLOR]

[COLOR=#252C2F][FONT=Helvetica]Function DigIn2(sPath As String, sWhat As String)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim fs[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim dDirs[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim dDir[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim fFile[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim c As Variant[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim tmp As String[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Set fs = CreateObject("Scripting.FileSystemObject")[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Set dDirs = fs.GetFolder(sPath)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]For Each dDir In dDirs.SubFolders[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]tmp = DigIn2(dDir.Path, sWhat)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Next[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]tmp = Dir(dDirs.Path & "" & sWhat)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]If tmp <> "" Then[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Do[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]lst.Add dDirs.Path & "" & tmp[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]tmp = Dir[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Loop Until tmp = ""[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Exit Function[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End If[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End Function[/FONT][/COLOR]
 
Last edited by a moderator:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
You must log in or register to reply here.

Similar threads

L
  • Locked
  • Question
Help Cleaning Up Code
Replies
2
Views
350
RoryA
RoryA
P
  • Question
Issue with button not running in spread sheet only in test mode
Replies
1
Views
205
6StringJazzer
6StringJazzer
S
  • Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
351
sspatriots
S
J
  • Question
Issue with Macros and renaming files
Replies
6
Views
518
kevin9999
K
S
  • Solved
VBA code - Borders for Table
Replies
2
Views
240
Spaztic
S

Forum statistics

Threads
1,215,391
Messages
6,124,673
Members
449,178
Latest member
Emilou

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top