Hyperlink Folder Level Limitations
Jun 15, 2007
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
View 2 Replies
ADVERTISEMENT
Jul 29, 2008
I have this formula that opens the current folder where my file is stored:
=HYPERLINK(MID(CELL("filename"),1,FIND("[",CELL("filename"))-1),"Open Current Folder")
This works fine. I'd like it to open the folder one level up from the current folder, but I really don't understand formulas well enough to know if this is even possible.
View 9 Replies
View Related
Apr 9, 2012
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]...
View 2 Replies
View Related
Nov 24, 2008
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 Related
Sep 4, 2009
I'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 Related
Nov 19, 2013
I 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] .........
View 5 Replies
View Related
Jun 6, 2008
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.
View 9 Replies
View Related
Sep 7, 2006
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
View 9 Replies
View Related
May 21, 2013
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?
View 3 Replies
View Related
Nov 25, 2009
I'm currently using a Lookup(2,1) that looks up an unopened excel sheet stored locally.
When I enter the formula I always get a #NA.
What I was wondering was if its a limitation of the lookup funcation, or my PC not having enough resources.
The linked XL sheet is ~10mb, and has over 8000 rows.
Below is my current forumla that results in a #NA
=LOOKUP(2,1/('[Monthly%20Agent%20AHT%20Tracker%20Current.xls]Quote'!$B$1:$B$8500&":"&'[Monthly%20Agent%20AHT%20Tracker%20Current.xls]Quote'!$E$1:$E$8500&":"&'[Monthly%20Agent%20AHT%20Tracker%20Current.xls]Quote'!$M$1:$M$8500&"Team Leader"&":James Kinsey"&":""Privilege"),'[Monthly%20Agent%20AHT%20Tracker%20Current.xls]Quote'!$Q$1:$Q$8500)
View 12 Replies
View Related
Aug 22, 2006
We have an Excel file that is located on the network and is accessed by numerous individuals. Should someone need access when another user has the file open, we get a message that the file is currently in use and it also lists the user id of that person. The issue is that a particular user will open the file and keep it on their desktop for hours at a time. Is there a way to limit the amount of time that user can have the file open? Is there something within Excel that I can turn on that should the file not be modified for a particular amount of time that Excel saves the file and closes it down?
View 4 Replies
View Related
Oct 12, 2006
is there a combo boxes limitation in a certain worksheets or workbook.?
if there is then how many? coz i got a problem , i have to make around 2000 combo boxes, if i save it , i got no problem , but when i closed it and then reopen , the problem occurs..
View 4 Replies
View Related
Mar 27, 2014
I recently bought a new Dell XPS 10 tablet with a keyboard. When I copied my spreadsheets from my old notebook, none of the countifs or countif functions are working, all popping out a zero. It work completely fine on my old notebook on which I have Excel 2010 installed. On the tablet I have the pre-installed Home & Student 2013 RT. Could this version have limitations to my functions? What can I do to get my functions working again?
View 4 Replies
View Related
Nov 19, 2013
I got this wonderful, beautiful spreadsheet/template i'm using now for my small business, and well, i've got it all tweaked and tuned so it looks beautiful on my screen (100% view in excel). I can look at it, and they way it looks, is the way it prints whether i opt for a PDF or (EEEEEK!) paper. I've sent this report and template i've put together with Mrexcel's support, and it's all primp and pretty and the person i send the spreadsheet to for review? he's looking at it at the same 100%, and lines are cut off when multiple rows are merged that have multiple sentences typed (technically reference to) in. he's tried to print both pdf and paper from his computer and had no issue. it comes out perfect. It only looks visually cut off. we're trying to avoid a second transmission back to me to reformat say something needs to be omitted. QA if you will.
my question: Is this some sort of issue relative to our screen resolution? is there a way i can optimally get what he is seeing when he views it electronically to be what i'm seeing electronically?
View 2 Replies
View Related
Jan 28, 2007
I have a macro that copys a worksheet a user-inputted number of times but throws an error after adding Sheet #62. The line it hangs on is:
Sheets(myTemplate).Copy After:=Sheets(i - 1)
Does anyone know if this is a problem with my code, a memory issue, or the workbook I'm using to copy? In the past when I've copied a lot of sheets in a workbook (to the same workbook), Excel sometimes won't let me add anymore but if I copy all the sheets to a new workbook it will sometimes let me add more sheets. I figured it was a problem with how Excel counts sheets.
Sub CopySheets()
myTemplate = ActiveSheet.Name
'First, jump through the validation hoops
Dim AddSheetQuestion As Variant
'Define the application input box question
showAddSheetQuestion:
AddSheetQuestion = Application.InputBox("Please enter the number of sheets you want to add," & vbCrLf & _
"or click the Cancel button to cancel the addition:", _
"How many sheets do you want to add?")
'Cancel or the X was clicked
If AddSheetQuestion = False Then..................................
View 9 Replies
View Related
Jul 20, 2013
I am using excel 2002 and I have found that when the workbook is shared and getting 2 or more users to open the workbook around the same time brings up a read only type prompt box for the 2nd ( and 3rd) user when the book hasn't fully opened for the 1st user/is still fully opening. Is this a limitation within excel or is there a way around this for example via vb code.
I know the more data a workbook has can cause it open a lot slower but is this the same for shared mode.
View 5 Replies
View Related
Dec 29, 2011
I'm trying to do a nested formula to automatically assign a region number based on what the County is. We have 88 counties we deal with, split into 7 regions. What is the best way to go about setting up a formula to do this, so I don't have to do it manually? I believe the last time I tried doing something like this, I received an error saying that Excel didn't support a formula as big as what I was writing.
View 9 Replies
View Related
Jun 14, 2007
I am trying to use the validation feature to calculate a check digit and the formula is too long to put in validation. I rewrote the formula to be shorter using math features instead of text, but apparently you can't use arrays in validation. Because of certain distribution constraints, using VBA is not possible so a UDF is out. Can either of these be shortened in anyway? I am not sure if it is even technically possible to solve this problem with these constraints.
Here is the "human readable" explanation of formula:
(Accept only 10 digit numeric value.)
Example Number: 123456789-3
Digits 1,3,5,7,9 are multiplied by 2 (weighted 5)
So 1,2,3,4,5,6,7,8,9 becomes 2,2,6,4,10,6,14,8,18Weighted 5's multiplied values then have their individual digits added (ex:12=1+2=3)
So 2,2,6,4,10,6,14,8,18 becomes 2,2,6,4,1,6,5,8,9
Weighted 5's sum is added to the sum of the Nonweighted 4 digits.
So 2,2,6,4,1,6,5,8,9,3 becomes 43
Add 24 to sum.
43+24 = 67
Checksum = (10-(67 mod 10)) mod 10
(Sum round up to 10 minus sum)
Method 1 ...
View 9 Replies
View Related
Feb 9, 2007
I think Match has trouble finding a value thats within a cell containing a lot of words.
Heres the example. I'm trying to find the word "Hello world" in cell B1, in Column A.
Column A
1 Lots of Text
2 Text Text. Hello world. text text
3 Text Text
Cell B1
Hello world
Column B2
Match("*"&B1&"*",A:A,0)
When theres only 1 sentence in Cell A2, the function works and returns 2.
However, when Cell A2 contains a few paragraphs the function returns #NA.
I don't necessarily need to use Match, if this is indeed a limitation does anyone of suggestions to work around, other than vlookup?
View 14 Replies
View Related
Feb 28, 2007
Excel 2003. When the Vlookup List becomes impractably long to select data from the cell dropdown. Is there an alternative prefered method to entering data from the long list?
View 7 Replies
View Related
Jul 2, 2009
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
View 10 Replies
View Related
May 14, 2014
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.
View 4 Replies
View Related
Mar 15, 2013
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".
View 9 Replies
View Related
Apr 21, 2014
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 Related
Jul 23, 2014
I 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.
View 9 Replies
View Related
Jan 30, 2014
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 Related
Oct 17, 2006
I 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.
View 6 Replies
View Related
Jul 23, 2012
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.
View 9 Replies
View Related
Jul 18, 2014
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?
View 2 Replies
View Related
Apr 13, 2009
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.
View 10 Replies
View Related
