I have a macro that creates a folder from an active cell in A1. I know want to have it create the folder and create a hyperlink.
Sub MakeFolder_Rev1()
FolderLocation = "G:TST shared filesHonda Problem & IPPAAR-IPP TrackingHTR PROBLEM TRACKING SHEETS67 KiHTR Communication & Documents"
CurrentRow = ActiveCell.Row
FolderName = Range("A" & CurrentRow).Value
[Code]...
code to create hyperlinks to all the workbooks in a folder? I Have about 52 workbooks in a folder and I like to place the links into another workbook so a user can open anyone they wish.
View 2 Replies View RelatedI'm trying to create a link to a folder and to insert it in a cell. The folder name is: "C:DISCO CTEMPLATES #1". I tried to copy that text and paste it into the cell. Excel recognizes that as a hyperlink but when I click the cell a message appears: "Cannot open the selected file" Could the problem be because of the "#" symbol?
View 2 Replies View RelatedI use the code below to autocreate a folder and hyperlink to it. It creates a folder when I enter a drivers name in column D it then turns the name into a hyper link to that folder - my problem is I have 2 files in the drivers folder on the C drive - one called wkd.xlsm (a small spread sheet) and one called notes.txt (a notepad document) I would like these files to be automatically added to the folder that is created when I add a drivers name to the D column - I would also like a hyperlink to be created to the wkd file in column H and a hyperlink to the notes file in column X.
Private Sub Worksheet_Change(ByVal Target As Range)
folderpath = "c:drivers"
If Not Intersect(Range("D3:D1000"), Target) Is Nothing Then
If Target.Value "" Then
If Dir(folderpath & Cells(Target.Row, "D") & " " & Cells(Target.Row, "C"), vbDirectory) = vbNullString Then
[Code] .........
I am writing a macro that scrapes information from other workbooks. I wanted to add a hyperlink on each line to the workbook that row's information came from. I tried both the VBA method of adding a hyperlink as well as the formula method. When I click on the link that was created, windows explorer opens up to a folder rather than opening the workbook. I have a feeling I am hitting some folder level limitation. The link is to a network share and here is an example of what it looks like:
\MYCOMPANY.CORPFILESCBOSEALDRIVEDeptROPSPT Billing2007 AdjustmentsMCBDApproved#01305.20075.25.07 - C - NAME.xls
I have a question about creating a custom drop down menu. In AutoCAD, I can create custom drop down menus that loads each time AutoCAD loads and i was wondering if excel have something similar. This menu would be added to the default menu list: "File_Edit_View_Insert_Format_Tools_Data...etc". I have searched online but i keep getting things about how to make drop down menus in cells but nothing about adding a main drop down menu.
What I want to do is create a menu with hyper links to all of the excel files we use quite often here at work.
I have already done this by just creating a sheet with a list of hyper links on it but would like to put it in a drop down menu instead if its possible.
I have found the code shown below that imports Folder Contents into a Worksheet in Excel, however i need to import a folder tree structure for a set of folders into a worksheet not the folder contents. Is it possible to do this?
Could the code i found below be altered to list the folders not the folder contents?
The code is:
Sub Print_Dir_Contents()
Dim Input_Dir, Print_File As String
Input_Dir = InputBox("Input the path containing the files you " & _
"want to list on your worksheet" & Chr(13) & Chr(13) & _
"for example:C:My Documents*.*")
If Input_Dir = "" Then Exit Sub
' If you want only to print a specific file type, you can
' substitute the "*.*" with "*.xl*"
' (for Excel files only) for the directory specified in the
' InputBox above.
If Application.OperatingSystem Like "*Win*" Then
Print_File = Dir(Input_Dir) & "*.*"
End If
I need a macro to create a hyperlink i.e....... I have an excel sheet with numbers in column A.....I have files in a folder that matches the numbers in column A....
How would I create a macro to create a hyperlink from the numbers in column A to the "matching" file names in a folder?
I have this code that looks through my worksheet once the conditions are met it will email, and in column "M" I put a hyperlink to where the document is stored. All works as far as the email format, even grabs the hyperlink but it’s not clickable in the email.
Here is the code.
I am outlook 07 and vista 07.
Option Explicit
Const Startingrow = 11 'Data starts on row ##
Const AlarmDelay = 183 'send warning
Sub CheckTimeLeftFac()
'References needed :
'Microsoft Outlook Object Library
Dim i As Long
Dim j As Long
Dim msg As Long
Dim Lastrow As Long
Dim WhoTo As String
Dim SubjectLine As String
Dim MessageBody As String
Dim olMail As Outlook.MailItem
Dim olApp As Outlook.Application
Dim strLink As String
I have alot of project folders on my harddrive.
All in format: I:/12345-costumer-projectname/
The five digits are unique for each project.
I make calculations for these projects using an excel file. In this excel I also type the projectnumber (cell J2)
Now i would like to make a button. When pressed, it checks the projectnumber cell J2, looksup the corresponding folder and saves the excelfile in PDF format in this folder.
I have found macro to find files in folders, but none which do the above.
I need a macro in a workbook to look at all the files in the same folder that have "*att*.xls" in the name and determine and copy from the range A15:W515 only the rows that have data in at least columns A, C and D. Each file will vary as to how many rows there will be and there are more than the files with "*att*.xls" in the folder. The data will be on the only worksheet in each file and the worksheet is named "G2WAttendee_xls" the data from all the files need to be copied to the file called "Consolidated webinar reports.xls" (I am using Excel 2003) and to a sheet called "Attendance Data" and added to the end of the last paste.
At the start of the macro the current file "Consolidated webinar report.xls" should be saved to a sub folder of the current directory and have the date saved added to the name. The sub folder is called "Completed reports". The data in the original file on worksheet "Attendance Data" should be deleted.
At the end of the process all the files that have had data copied from them should be moved to the sub folder "Attendance reports consolidated" (This could be done as each file is closed if that is easier).
I have headings in row 1 of the "Attendance Data" worksheet that match the headings in the various files in the folder (which will always be in row 14 of the individual "*att*.xls" files).
The folder with all the files and the "Consolidated webinar report.xls" file is at path "Z:P and S MEvaluationsWebinar series 2012-13TB".
I have up to 50 workbooks in one folder with data in a specific range. I also have one workbook which includes additional data, including conditional formatting and dropdowns. I need to copy the desired range from the first workbook in the source folder to the second workbook, then save the latter to my destination folder, using the same name as the first. I need to repeat this process for all workbooks in the source folder.
View 2 Replies View RelatedI have an Excel VBA Macro for creating/logging drawing numbers. Each drawing belongs to a job number. Each job number has a folder name containing the job number followed by a description (ie 999999 - bracket assembly) for storing drawing pdf's. The job number is only known as a 6 figure number in the drawing creation process BUT for the PDF saving process it is a string value...as my example above
I have to change my process by pre-creating the Job Number folder, then have the macro look for the appropriate folder by searching the parent folder for a sub folder containing the job number (always the first 6 figures).
I want to insert in my macro some code that searches...
The parent folder for a folder containing the job number. The macro value for the parent folder is P:engineeringdrawings (this never changes). the macro value for the job number is iOpenair (it's an entry that is entered as start the macro). Imagine the value for the job number is "999999"...so the search would be for "999999*.*" The code needs to search the parent folder, find the folder name that begins with a six digit number. Capture the complete name of the folder and store that name as a Dim value that I can call up elsewhere in my macro.
I have hyperlinks betwene one worksheet to another - they only only hyperlinked thourh column & rows (eg: A100) etc. How can I lock these hyperlinks but still allow users to insert new rows without losing their place?
View 2 Replies View RelatedI am having a slight problem copying hyperlinks. I have written some code that sorts data by date and then creates a simple diary. It originally placed the name of the event in the new diary sheet. What I would like to do is instead of copying the name into this new sheet copy a hyperlink that I have created to the company's website for that event.
I can create the hyperlink using hyperlink(B1,A1) where A1 is the company name and B1 is the web address but since the new sheet will not have the underlying data I need to actually copy the values and format of the hyperlink rather than the formula.
I am creating a document for work that automatically generates hyperlinks to pdf files in a given sub folder. Currently I have a section of code that opens a window to choose the folder location.
Code:
'Prompt user to select a directory
Do
Problem = False
Set ShellApp = CreateObject("Shell.Application"). _
Browseforfolder(0, "Please choose a folder", 0, "c:\")
On Error Resume Next
[Code] ........
What I actually want is to not have the window open, but excel automatically choose the sub folder location.
The directory looks like this
Z:ClientProjectDRAWINGS-2 RECORD COPIESPDF
The xlsm file is stored in the DRAWINGS folder, and I need it to point to the PDF folder.
I have a list of items in an excel sheet... 500 of them... and there are images for them in an other folder... The problem is those images are in 1000's and I only need those 500 images.. Images are saved as "SKU.jpg eg NS2354.jpg and we have an SKU column in excel as well .. SKU and their images have same name...
Is there any solution to pick up only those 500 images from those thousands of images?
Is it possible to save those 500 images in an other folder?
looking for some code to save to a destination
destination address is
C:Documents and SettingsstDesktopOJF
now the problem is OJF has folders named 1 to 500
so if cell d5 = 487 it will need to look in the above desination and then open the folder and save it there.
I would like to create a code that will open all workbooks in folder "A" one at a time and then close the work book after another code is executed. So one workbook will open and while the workbook is open, I will call another code to format the active workbook and then the active workbook will be saved in folder "B" and closed and go to the next workbook in folder "A" to start all over. The folders are saved on the desktop.
View 5 Replies View Relatedif it's possible to use XlDialogOpen to open up a folder that you only have part of the name.
For example, say you have the following folder paths:
C:Main FolderSubfolder1Smith,Billy 2413
C:Main FolderSubfolder1Brown,Charlie Ray 2416
C:Main FolderSubfolder1Bunny,B 3619
C:Main FolderSubfolder1Fudd,E-G 1864
What I'm hoping to do is just look for the 4 digit number on the end finding that folder name and opening it up. The name conventions before the 4-digit serial number are quite random. Sometimes it includes their full first name, other times it's just an initial for the first name and other times there are other characters included in the name. The one thing that is constant is the 4 digit serial number on the end.
how I could get the Open dialog box to pop up and have the chosen folder number open?
Is there a way I can from vba cut a file from a folder and put in to another folder? And also create a folder? If yes, how can I do this?
Is it better to use dir or scripting.filesystem object?and if there is an advantage to use one over the other.
I am trying to do is have a hyperlink go to another sheet after searching the second sheet for the same value. I have a sheet labeled "IQI" which has the total number of cases for a certain condition. I have another sheet labeled "IQI Cases" which lists out each case individually.
I want to be able to click on the Condidion in the IQI sheet and have that take me to that specific spot in the IQI Cases sheet where the cases for that condition begin.
Right now I just have a simple hyperlink to a range of cells. But each month a report is generated and the range will be different and I have to go and change the range each time. So I guess I want a hyperlink and a lookup function combination.
I dont know if this would be a Macro or a formula.
I am trying to use hyperlinks to navigate documents. I created a html document with excel and used hyperlinks to PDF documents.
At first the hyperlinks worked fine, opening the PDF software and in turn the PDF document.
Now when i click the hyperlink in Internet Explorer for the PDF document to open, it brings me to a blank HTML document.
In other words, the hyperlinks to the PDF's no longer open the PDF software and document.
Imagine if the word below was a hyperlink to a webpage somewhere;
House
I would like to know how to get excel to look at this paticular page and return that one word to a cell, so in this example, I would like cell A1 to to contain the word house.
I do not need any information about where the link is referring to, I already have this information.
All I need is for excel to look at this link and tell me what it is called.
I'm having a problem getting a hyperlink to work when adding it to a vlookup. The vlookup is looking at a page number and returning the named drawing from an Auto Desk Inventor File. I'm trying to get the returned drawing name to become a hyperlink to open the drawing. I would rather have this added to the formula so if a page number ever changes the hyperlink will change to the correct drawing also. Here is what I have so far but it is just returning an error of " cannot open the specified file". =HYPERLINK(IF(E5="","",(VLOOKUP(E5,'Inventor Import'!$A$2:$E$200,5,FALSE))))
View 4 Replies View Related"A12" says "Click for new line". I want that to be a hyperlink so if someone clicks it, it will automatically take you to a fresh new line to input there information.
Row 14 and above is a frozen pane.
Anything from row 15 below will contain data.
I could have 1,232 rows and the users who are using this spreadsheet probably won't fancy scrolling down for ages to find a new row to enter the information...
I have tried searching the forums and google but most of them explain how to use hyperlink with regards to different cells or worksheet or dedicated internet links.
i have a little twist to that and am unable to figure out exactly how to make that happen?
My requirements:
cell a3 has value 321011
now this needs to be hyperlinked iteself meaning
cell A3 will display the same value 321011 but should be hyperlinked to
http://support.microsoft.com/kb/321011
I have just been told that one of our servers is being replaced this weekend. I have thousands of hyperlinks in excel docs that reference this server. Other than manually reestablishing the links one by one to the new server, is there a way to do some kind of global change to replace the old server name with the new server name?
These are not hyperlink formulas, but rather Insert>>Hyperlink.
Or, is there a way to convert these hyperlinks to hyperlink formulas that would omit reference to the server and thus not be broken during the conversion?
Grasping at straws because I cannot envision having to relink these one by one.
how to create a Hyperlink to a specific worksheet in a workbook instead of just the workbook?
View 2 Replies View RelatedSuppose I have and excel workbook with 100 sheets and I want to click from one sheet to another. I created a table of all the sheets and hyperlinked the sheets then copy/pasted the table into each worksheet but that seems primitive. What if I have to revise the table? I will have to do it hundred times !
Is there a smart way to accomplish this using one smart master table that I can copy and paste into each sheet or somehting similar?
