Sub dial() ' ' dial Macro ' Range("D10").Select End Subcan someone pls help
Sub CreatePDFLinks() ' The Usual Suspects On Error GoTo ErrHandler With Application .ScreenUpdating = False .Calculation = xlCalculationManual .StatusBar = "Please wait. Creating PDF Links..." End With ' Dim Vars Dim baseBook As Workbook Dim i As Long, objExcel As Object, objSearch As Object Dim strPDFLocation As String, strFoundPDF As String Dim strFoundFile As Variant Dim intCouponNo As Integer, intHighestCoupon As Integer ' Decalre Vars Set baseBook = ThisWorkbook Set objExcel = CreateObject("Excel.Application") ' Define the Location to Check for PDF's In strPDFLocation = InputBox("The Link creation process can take some time to run. Please enter the full directory path of the folder to search for scans " _ & "(example: c:scans): ", "Scan for Coupons") objExcel.Visible = False Set objSearch = objExcel.FileSearch objSearch.LookIn = strPDFLocation objSearch.SearchSubFolders = True ' Loop Through the List of Ticket Numbers from A1 down on the First Worksheet ' Can easily be changed to used with a range passed to the sub ' depending onw hat works best for you baseBook.Activate baseBook.Worksheets(1).Activate i = 1 While Range("A" + Trim(Str(i))).Value Empty ' Check For Any PDFs with That Ticket Number objSearch.Filename = "0" + Trim(Str(Range("A" + Trim(Str(i))).Value)) + "-*.pdf" ' Its found some so find the Highest Coupon Number ' This bit isn't particulary elegant though If objSearch.Execute > 0 Then intHighestCoupon = 0 For Each strFoundFile In objSearch.FoundFiles intCouponNo = CInt(Mid(strFoundFile, InStrRev(strFoundFile, "-") + 1, 1)) If intCouponNo >= intHighestCoupon Then strFoundPDF = strFoundFile End If Next ' Add the Hyperlink to the Cell but don't change the text Range("A" + Trim(Str(i))).Select ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=strFoundPDF End If objSearch.NewSearch i = i + 1 Wend objExcel.Quit ExitHere: ' And Now We Rest On Error Resume Next Err.Clear With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic .StatusBar = False End With Exit Sub ErrHandler: ' Catch Any Errors MsgBox Err.Description & " (" & Err.Number & ")", vbExclamation Resume ExitHere End SubNow I was wondering if it would be possibe to modify this somewhat as follows. I would like the macro to copy of any pdf it finds and save them to a (user defined) folder.
================== Sub HyperlinkRem() ' ' HyperlinkRem Macro ' Macro recorded 11/19/2007 by Cabrera Range ("D2")select Selection.Hyperlinks.Delete 'The Loop runs until there is nothing in the last row Do Selection.Hyperlinks.Delete ActiveCell.Offset(1, 0).Select Loop Until IsEmpty(ActiveCell.Offset(0, 1)) End Sub ===========================Can anyone help me complete the correct sequence?
VB:As you can see, it has B17 cell address as fixed. I would like to have it as a selected cell meaning where the cursor is.Create_New_Ship_Code() ' ' Create_New_Ship_Code Macro ' Macro recorded 1/19/2011 by ' ' Range("B17").Select Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:also it will replace where ever that name comes without first confirming the user(to replace or not for that found word in word doc), we need here in such a manner that after copy it ask yes to replace that word in doc if no go to next word...etc, and STOP to no to proceedthanks,If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
'This section builds the Hyperlinks Dim ShName As String Dim ShCt, N As Integer Dim Sht As Object ShCt = ThisWorkbook.Sheets.Count Range("B8").Select For Each Sht In ThisWorkbook.Sheets If Sht.Visible <> xlSheetVisible Then GoTo Skip ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ Chr$(39) & Sht.Name & Chr$(39) & "!A1" Selection.Hyperlinks(1).TextToDisplay = Sht.Name ActiveCell.Offset(0, 1) = Worksheets(Sht.Name).Cells(7, 3) ActiveCell.Offset(0, 2) = Worksheets(Sht.Name).Cells(7, 5) ActiveCell.Offset(0, 3) = Worksheets(Sht.Name).Cells(11, 3) If ActiveCell.Column = 7 Then ActiveCell.Offset(1, 0).Range("A1").Select Selection.End(xlToLeft).Select Else ActiveCell.Offset(1, 0).Range("A1").Select End If Skip: Next ShtUPDATE: #1 Solved by adding the following code:
2. The Table of Contents macro is activated via a button on the "Directory" sheet - However sometimes it executes on random sheets in the workbook. I have been unable to figure out why this happens. Is there a bit of code I can add that would only allow the macro to run on the "Directory" sheet?
Thank you for any and all help...
Sub CUST_PO_LINK_MAKER() Dim MyFolder As String Dim LASTROW As Long Dim FIRSTROW As Long Dim I As Long Dim f As Integer Dim MyFileCount As Integer Dim WS As Worksheet MyFolder = "U:Engineering_AdministrationGENERALSpecial ProjectsTC CircleTrialCUSTOMER PO" Set WS = ActiveSheet FIRSTROW = 6 LASTROW = Range("R" & Rows.Count).End(xlUp).Row For I = FIRSTROW To LASTROW FindText = Range("R" & I).Value '- IGNORE BLANK CELLS If FindText <> "" Then MyFileType = "*" & FindText & "*.*" ' = "*Test*.*" '- CHECK FILE NAMES With Application.FileSearch .NewSearch .LookIn = MyFolder .Filename = MyFileType '- RESULTS MyFileCount = 0 If .Execute() > 0 Then MyFileCount = .FoundFiles.Count For f = 1 To MyFileCount MyFileName = .FoundFiles(f) WS.Hyperlinks.Add Anchor:=Range("R" & I), Address:=MyFileName, TextToDisplay:= _ Replace(.FoundFiles(f), MyFolder & "", "") Next Else MsgBox ("Search for file names containing : " & FindText & vbCr _ & "No matches found") End If End With End If Next I End Sub
Range("P24").Select Selection.Copy Application.Goto Reference:="R4C10" End SubBasically the bit in Italics I need to be the contents of cell P24 right now, as the macro is executed.
Sub CREATEPDFDIRECTORY() Dim path As String Dim f As Integer path = "filepath" f = 1 With Application.FileSearch .LookIn = path .FileType = msoFileTypeAllFiles .Execute For i = 1 To .FoundFiles.Count If UCase(Right(.FoundFiles(i), 3)) = "PDF" Then Range("A" & f).Value = .FoundFiles(i) ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & f), _ Address:=.FoundFiles(i), TextToDisplay:=.FoundFiles(i) f = f + 1 End If Next i End With End Sub