Create A List Of Files With Clickable Links
Mar 17, 2006
I've found countless, very useful macros that do 1/3 of what i need. My needs:
1) A macro to look in a set network folder, and generate a list of Excel files, and display them (1 per row, just the file name if possible)
2) Each file name is a hyperlink to open that sheet
3) A macro to look at the file listed above, then list in the column B a value from a set cell. So it would ideally output:
1| RH0018.xls A1CellValue
2| RH0019.xls A1CellValue
3| RH0020.xls A1CellValue
View 2 Replies
ADVERTISEMENT
Jan 5, 2012
I had someone look up a price on a website, for a list of part numbers, and copy the link to the website page into my spreadsheet.
Now I want to transfer the price and link to my main spreadsheet.
I know how to use VLookup to get the info to my main spreadsheet but when I transfer the link using VLookup it does not come as a hyperlink that I can click on and be sent to the webpage. It is just text.
How do I convert the links to a hyperlink that I can click on and be sent to the webpage?
I have 1200 records.
View 6 Replies
View Related
Mar 14, 2008
We use Excel 2003 and 2007 to track labor data for a field service company. The field tech's complete daily forms and the office staff copy/paste this data into weekly billing worksheets. They use naming conventions to show the jobs and dates in the file name (Daily Labor - Job1 - 031308.xls and Weekly Labor - week ending 3-16.xls. What I would like to do is link the daily data to the weekly forms as they come in. Is there some way to do this when the job names and the dates are always different? Can I tell Excel to expect data files based on the naming conventions as in the Weekly is named "Weekly Labor - Week Ending 3-16.xls so data files named LaborJob1310.csv, laborjob1311.csv would automatically update the weekly sheet? I can put these in a folder and link them, but I don't know how to provide an easy way to do this for the upcoming week.
View 4 Replies
View Related
May 17, 2013
i have many excel workbooks in a folder i want a macro that will get the names of all the files and make the file names so extracted as a hyperlink to open the files.
View 1 Replies
View Related
Oct 20, 2007
Is it possible to create a VBA script that will list the filenames of files in a set directory? I have about 100 directories, each containing 1000 files and they need to go into an excel spreadsheet (each file on a new row)so they can be audited... ;(
View 3 Replies
View Related
Jul 26, 2013
I have a folder with 20 Excel files. I'm trying to create a master list of all the tab names. I can see all of the files opening, but it only copies some of the names.
VB:
Sub GetTabNames()
Dim wkBook1, wkBook2 As Workbook
Dim stFilePath1 As String
Dim FileList(1 To 18) As String
Dim iLoopSheet, iLoopProg As Integer
Application.ScreenUpdating = False
[Code] .....
View 2 Replies
View Related
Feb 8, 2013
My problem is to create a macro that will list all folders, subfolders and files in an order in excel. So that I know which folders and files belong to which main folder, more like a hierarchy.
For example,
Sourcefolder
Subfolder 1
File1
I have code that list all files in folders and subfolders, but it does not put them in a hierarchy fashion.
View 8 Replies
View Related
Nov 19, 2012
I have been having problems lately with excel files on Network drives. I have one file that I created on 11/12/2012. However, on Friday that file showed it had been modified by myself on 11/13/2012. I did not do this. As a note, this file contains macros based on a button that is clicked from a worksheet.
I then copied data from this worksheet into another file, creating a link that I did not know was there with the worksheet and the macro. From the first workbook, I also copied in data collection from two other worksheets, also I believe with a link to the first workbook.
I did a test on the first file this morning and found out you can open a file, make no changes, and close the file. Excel will ask you to save the file. If you say Yes, the file will show being modified with today's date and your userid, even though nothing really changed.
What I am actually trying to find out is that can the original file be altered somehow that will automatically update the linked file? Or, when I open the linked file, do I have to say "YES", I want the updates before the linked file is updated?
There were some questions as to how a value showed up in the link file (or actually ended up being removed) and when I looked at the original file it was also removed. We checked on stability across the network drive but found it to be okay, now we are looking at the link issue.
View 2 Replies
View Related
Mar 3, 2007
This workbook contains links to other data sources.
*If you update the links, Excel will attempt to retrieve the latest data.
*If you don't update the links, Excel will use the previous information.
Note that data links can be used to access and share confidential information without permission and possibly perform other harmful actions. Do not update the links if you do not trust the source of this workbook.
I've stopped trying to rectify the situation and just click on cancel. So does everyone else. Big mistake. If there is a file that changes from time to time, and you're relying on a link to it to make your data correct, and some one moves or removes it! you get the above message. But you have no idea it's your very important file that has been deleted or moved. And Excel as the message says, uses the last data it had access to.
Is there a way to force Excel to display the #N/A error for the broken link instead of telling the lie? Well really, if the file’s been deleted or moved, Excel has no idea what the true value of the "Link" is and is lying when it puts up that last known value and really doesn't tell you except for that opening message.
Very frustrating, the Bill Of Material was moved and then changed, and my Excel file reported the old value just like that.
I've got a huge file with lots of pages and all sorts of people do lots of things including making links to files that after a while get moved or deleted. I can't chase them all down.
So, is there a way to force Excel to "Crash" and put up an error message in the cell where the link is supposed to be?
View 9 Replies
View Related
Dec 6, 2006
I want to link data between two files, which are always in the same position relative to each other, but can be copied to other locations. Here is an example:
D:Job 1SourceJob Info.xls
D:Job 1SlaveClient Form.xls
Cell B1 in Client Form.xls refers to Cell A1 in Job Info.xls. This link is created when both files are open. When Job Info.xls is closed, the reference in Client Form changes to 'D:Job 1Source[Job Info.xls]Sheet1'!$A$1.
Then, I copy and save Job 1 as Job 2. Thus, the second set of folders are: ....
View 7 Replies
View Related
Aug 5, 2014
I am working on a file that has multiple worksheets with many links, lookups and formulas; some between the worksheets and some external to another Excel file. The plan is to use this file as a template and copy it over and over with new names. Once I save the file as a new name in a new folder on my network, will I lose all these links, lookups and formulas? Or is there a way to maintain the links or do a global change of the formulas (i.e., the original file name is "TEMPLATE" and the new file name is "PROJECT!1")?
View 2 Replies
View Related
Jun 12, 2013
I have a master workbook (that I will call 'A') with a number of links to other workbooks ('B' to 'Z'). 'A' is designed to collate and display the data held in workbooks 'B' to 'Z'. The links all work fine until the 'B' to 'Z' workbooks are updated with new data, at which point, the links break with a "Error: source not found" error.
This is caused by the process by which we update the 'B' to 'Z' workbooks. We have to delete them, and replace them with new files, that have the same name.
Is there any VBA code or something that I could run that will update the links to pick up the new files?
View 2 Replies
View Related
May 12, 2014
I'm trying to set up a macro so that: in column C I will see the current (old) links being used by the workbookin column D I will input the new links I want to use in the workbookthe macro will open all those files in column Dthe macro will then replace the current (old) links with those listed in column D
I have the following so far, but something doesn't seem to be working - it gets as far as opening the first new file but doesn't open any others and doesn't change any links (although I've tested the part to change links separately and this seemed to work ok).
Sub linked_sheets()
Dim LinkedBooks As Variant
Dim i As Long
LinkedBooks = ThisWorkbook.LinkSources()
For i = LBound(LinkedBooks) To UBound(LinkedBooks)
Cells(i + 3, 3) = LinkedBooks(i)
[Code] ..........
View 1 Replies
View Related
Apr 9, 2009
I have a large spreadsheet that has links to CSV files. Every month I need to create a new folder and put the CSV files into that folder, all the CSV files from month to month have the same name for each worksheet.
I'm trying to do Find and replace on the Folder name within the links to change say, February to March so I can just add the CSV files for each month and the spreadsheet will link to the new CSV file in the folder.
I have even tried adding all the February CSV's to the March folder so that when I overwrite the CSV's it will update the master spreadsheet, but it keeps asking me where to look for the folder.
Here is an example of a link:
='\FileservermsbilBRANCH REPORTSBM ReportsFebruary[TItemToDispense@40186 Export Dispensed Item Data-00.CSV]TItemToDispense@40186 Export Di'!BM2
As you can see the folder is named February within the link and I need to replace it with March.
View 9 Replies
View Related
Jan 14, 2009
I have a workbook that archives by date and is saved for 1 month at a time. At the end of the month, all the archived workbooks are moved to another location and are no longer needed.
The problem I am having is I don't know how to break the link between the master and all the saved workbooks. So when I open the master it is looking for the old workbooks in the folder location and they do not exist. How do I break this link?
View 5 Replies
View Related
Sep 12, 2010
I and my organization have many files created using Excel 2003. I have now upgraded to Excel 2007 and soon I discovered a huge problem.
- When opening a file using Excel 2007 that was created with Excel 2003 the links in that file is automatically updated even though I get the edit links dialog and choose not to update.
After searching for a solution on the Internet I realize that this is a problem that exist when Excel opens a file created in an earlier version. Microsoft has no solution.
I need to be in control of which links that are updated and not.
Going back to excel 2003 is not an option. Saving the source files in Excel 2007 is not an option.
View 5 Replies
View Related
May 30, 2013
I have links between Excel files and Word files. I use these files for multiple projects. When I copy them and paste them to a new folder the new files would be linked together. For some reason this has stopped. Now when I copy, the Word file has links to the original document, not to the copied document.
View 7 Replies
View Related
Sep 13, 2007
I am interested in finding out how to link worksheets. For example: the bottom tabs for worksheet1 says contacts. tab for worksheet2 says projects. I want to link from worksheet1 to worksheet2 by clicking on a link that will take the person to the project. On worksheet 2 I have more than one project. THerefore, on worksheet1 after all the content I'd like to have a link that says Project 1 with a link that sends the person to the next worksheet. Same with Project 2. I know this is possible. I have already added the formula. Now I need information on how to insert a link that can be clicked on that will go to the next page.
View 4 Replies
View Related
Aug 29, 2006
I am trying to open set of excel files to fetch data starting from a master excel file where the links are given using a loop. Some of files given as links are either absent or the link is wrong.
* What is the syntax to find if the link is correct/present
* What is the syntax to find if the file is present in specified location
* What is the syntax to find if the file is password protected to open
* I use error handler to resume the next statement if OPEN statement fails.It works fine inside the loop only for first two files.
For example if the third link is errorneous the error handler fails and RUN TIME ERROR occurs.
View 5 Replies
View Related
Jul 19, 2006
My first post at last.
I've recorded a Get External Data macro and wish to modify source and destination components. I would like to replace the absolute link to the file name with a variable and the Destination range to the active cell at the time I run the macro.
This is the is the macro code line that handles the source and destination components I want to modify.
Range("AQ2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:filename.csv", Destination:=Range("AQ2"))
View 3 Replies
View Related
Jan 8, 2009
I'm trying to create a formula similar to this:
=Calculations!(Indirect("N"&A2)-Indirect("AB"&A2)+Indirect("AA"&A2)
The idea is that the user will enter a value in cell A2. That number will determine the appropriate rows in the formula above.
View 6 Replies
View Related
Sep 25, 2013
I have two spreadsheets that are linked in Excel 2007. They both hold very different information about the same projects, so each row shares some information. However, they are both very large and I do not want to combine them in case the whole spreadsheet becomes unusable. I'm not allowed to use a database
When I save Spreadsheet 1 in a different location, so that I have a backup of the precious data, the link in Spreadsheet 2 changes to show that new location. I want the link to stay at the old location.
Is there a way to stop the link changing when I use SaveAs, so that when I open Spreadsheet 2 it refers to the original location of Spreadsheet 1?
View 2 Replies
View Related
Feb 3, 2014
So I got a list of months in a drop down box that when selected goes to the appropriate month tab that works great. But what I want to do is hide those month tabs that are in the drop down list. I'll add another drop down list with the other tabs to reference back to all the other month tabs so the user can move around. Only problem is when I do hide them they are unavailable meaning when selected it doesn't go the the appropriate tab that's hidden. I want to hide them from the user and still be able to go to them, I'm not worried about the user breaking the sheet. See attached example.
There is VB code in the "Home" sheet. Tab Links.xlsm
View 2 Replies
View Related
Oct 31, 2007
I have some code to generate hyprlinks from a folder
View 13 Replies
View Related
Nov 29, 2006
Is there a way to paste a list of the Links in a sheet somewhere, so that it can be easily seen, reviewed and printed out for an audit trail?
(i.e. an analogue to the F3-PasteList that gets you a list of the Names in a sheet.)
View 9 Replies
View Related
Jan 23, 2009
I would like to set up an Excel spreadsheet for conducting a time and motion study in a workplace setting where someone follows an employee around to document how much time is spent on a variety of activities. Since the person would have a mobile tablet PC to walk around with and track the data, I would like to capture 3 fields of information:
1) Category of function being performed
2) Start date/time of function
3) Stop date/time of function
For #1, I would like to have a drop down box that appears in each row of Column A with a predefined list (i.e. Activity A, Activity B, Activity C, etc.). For #2 and #3, I would like to create a macro that "timestamps" the current date/time when a blank cell is "clicked".
View 5 Replies
View Related
Apr 15, 2009
I am trying to create a password on a clickable button in Excel. I have put the following code in:
View 11 Replies
View Related
May 31, 2009
I have joined here with a great expectation. I used the Forum MR Excel , i posted my question there but no one replied . I hope i will get the answer to my question here. My question is
I Have Combo box
When i click on it i get three Things which i have inputed in the list .
1 ) Machine
2 ) Car
3 ) Bike
With each list , a seperate sheet is assign with complete details like machine for machine sheet , car for car sheet and bike for bike sheet .
Now what i want is when i click on the machine i want the Machine sheet to popup within the same workbook , and when i click car i want the car sheet to popup and same for Bike .
I tried to Assign Macro , but for each list , i am getting the same / last result
here is the code
View 14 Replies
View Related
Jan 22, 2008
I have a userform which takes a large amount of sequential geographical data and builds a theoretical model of my layout whithin excel. However I would now like to incorperate an interactive aspect to this userform. I have written code which draws a line diagram whithin a seperate CAD app and imports it as a picture to the excel userform each time the user updates the data, but I would like to be able to select different areas whithin the data by clicking in the appropriate place on the picture in the form. I know this kind of interactivity is a bit beyond visual basics line of duty but my model MUST stay whithin the confines of excel thereforwe my front end must remain VBA for the time being.
My other option is to build up a diagram out of smaller components (boxes etc) whithin the userform. This would be entirely acceptable, however I'm not too sure if its possible to 'create' new pictures on the form from whithin the code.
View 9 Replies
View Related
Nov 18, 2008
I've managed to get the following macro working except for one small thing - the link to the folder where the spreadsheet (that will be attached to the email) resides (which I got to work fine in a macro to send out tasks) just ends up as plain text and is not clickable
Make this link clickable in the email so that when the recipient receives the email they can just click on the link and go straight to the relevant folder please? (I've highlighted the line with what I would like to work as a link in red)
View 2 Replies
View Related
