Hyperlink Locates File Regardless Of Its Location |
Hyperlink Locates File Regardless Of Its Location - Excel |
|
I have a 100's of hyperlinks that take a part number from a cell and
then point to and open a corresponding pdf drawing located in
z:\\server-h\vol1\data\xx.pdf
However some of the pdf files are located in '...vol2\data\xx'
Can the hyperlink not have a formula or some msdos command in it so
that it can search for a particular filename by itself on the whole
hard drive. This way if the pdf files get moved around there will be no
problem with the hyperlinks? I'm using Excel XP 2002.
Thanks,
Avais
Similar Topics
I have a number of different files that I often need to run a macro on. In order for me to do it on the 75-100 files I have at any given time, I need to open one, run the macro, close and save, then open the next one.
Is it possible to write a macro that will start with the first file in a folder, open it and update links, run a macro, save and close, and open the next file in the folder until it has open all the files in the folder.
I have experience with creating macros that reference different workbooks, but not sure how to go about opening files with different filenames (without referencing the exact filename).
I'd like to be able to have basic code for opening, saving and closing, opening next file, saving and closing, etc. and input the macro I'd need to run in each file in the appropriate location. Is this possible? Any help is greatly appreciated!!
Thanks,
Jason
Perhaps a better solution is to use a workaround that relies on the HYPERLINK function to refer to whatever is selected in the drop-down list. For instance, if you have your data validation drop-down list in cell A1, then you might put the following formula in cell B1:
=HYPERLINK(A1, "Goto Link")
The solution directly above provides exactly what I am looking for
in the field where I write the formula, but it fails to hyperlink.
I have created a drop down list and linked each one of them to a
specific worksheet. When I select them individually they link to
appropriate worksheet. But when I select them in the drop down
list I receive the following error when I select the Hyperlink in
cell B1 as directed above.
"Cannot open the specified file"
Any thoughts?
Bob
Am in need for this solution very badly and what could be a better place than excelforum !
I have an MS Excel File (2007 version) sample file attached, which has name, designation, blood group and so on. The last column is for hyperlinking photographs of individuals.
In the same folder where I have saved this excel file, are lying photographs of individuals. While scanning the photographs, I have saved them serially i.e. 1,2,3 and so on.
In the Excel file, in last column, I have given the respective serial numbers. In order to hyperlink one has to select that particular Cell, press Ctrl K and you automatically go to the folder containing individual photographs, you select that photo and OK.
Problem :
I have to do this hyperlinking one by one and if there 1000 photos, lot of time is wasted.
Solution Needed :
Just in case of excel formula, which we copy and paste, Can I get a command by virtue of which the column titled Photo or column next to it gets automatically Hyperlinked to respective photo WHEN I copy and paste such command to all cells in that column.
Thanks a million and warm regards ::: Jack
but how to open hyperlink with the keyboard... any shortcut...
regards,
nishith
I am working with an Excel spreadsheet and saving it as a .csv file in order to upload to an application that parses out the .csv data as transactions. The system requires .csv files, so this is how I need to save my doc (with this extension). I have been successful at preventing Excel from coverting that long number into scientific format. I have saved as a TXT file, pasted the longer number and it displays correctly. That is all good. But I have to save as a .csv. So if I do that, close the Excel window, and then open again (as the .csv file), the numbers are back to being displayed in scientific format. I have tried creating an Excel doc from scratch and entering text in Text format, to see if this created a cleaner file. But again, the second I save as .csv, close the window and then open that file up again, that dang scientific format is back.
Does anyone have any idea of how to work around this? Once I have successfully gotten the numbers to display as the long-chain number, how can I get them to "stick" so that they don't revert back to scientific format when I reopen the file?
Thanks so much for your help!
It does this using the indirect function because it needs to concatenate the
path and filename from other cells.
Problem is that if workbook B is closed, the cell in workbook A shows #REF!.
So can indirect work using closed external files or must the external files
be opened?
Is there a clever way of making that work?
Thanks!
I dont want it to actually make the save though as I want the user to have the chance to check that the save location is correct and the option to change the filename if required.
Cheers.
So AHW18.787 becomes AHW18 787 and AHW18787 with
=SUBSTITUTE(A1,"."," ") and
=SUBSTITUTE(A1,".","") respectively, and
AHRTW-A18-7007 becomes AHRTW A18 7007 and AHRTWA187007 with
=SUBSTITUTE(A1,"-"," ") and
=SUBSTITUTE(A1,"-","").
What I need to be able to do is merge these formulas into one, so that no matter what format the part number is in A1, I get a version of it with spaces in B1, and a version of it with no spaces in C1. I just have not been able to nest it all together - is it even possible with this command?
Thx
How do I find out what the current User's desktop folder path is each time the Marco is run by a different User?
Example User's path: 'C:\Documents and Settings\jfarc\Desktop'
Where 'jfarc' is the name of the current User which, will of course change with every different User that runs the Macro.
Also, is there a way to pull out of Excel what is the current User's 'Options | General | Default File Location' entry? Which may differ from the above directory.
I am familiar with and use the following coding for Opening/Saving files to the current directory of the opened workbook, but it only gives the path of the existing Excel workbook and not the current User's Directory Path:
Dim wbThis As Workbook
Set wbThis = ThisWorkbook
ChDir wbThis.Path
Make sense? I'm a bit confused myself. Maybe just a list of basic workbook-switching techniques or commands would be useful.
- Michael
This macro is amazingly straight-forward:
Public Sub SaveAsA1()
ThisFile = Range("A1").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub
I'm totally lost on this "amazingly straight-forward" macro!!!! Could someone help if I tell you the SAVE location? It's T:\COMMISSIONING\IJT\TIMELOG project\Staff#1. I'm just not certain what value I'm replacing in the Macro above.
Any help appreciated..
Excel 2002 spreadsheet and it has disappeared.
I saved it routinely as I was making many changes updates etc. I opened
original file --stuff.xls --- made changes and saved it as again as
stuff.xls. I have done this several times before.
Today, when I go to open stuff.xls, it is nowhere to be found!!!!! I have
done a search, for any file modified since Thurs and nothing has appeared. I
have checked the recycle bin, this did not go into auto recover nothing of
the like. I am just traumatized, because I have NEVER had anything like this
happen before.
I even thought perhaps I didn't save it as often as I thought, (I know that
I did) but I remembered that I saved it at least once and I can't even find
an Excel file that has been modified since Thurs!!!
Does anyone have any idea what could have happened to this file?? BTW, I
have prayed, rebooted, run spybot, called witch doctor, all with no success.
I have never had a file to just disappear with no trace. Any help would be
greatly appreciated.
Thanks in advance---
All of the buttons were working fine. Then I saved and closed the workbook and went to lunch. Now when I open the workbook, the buttons don't work! When I click them nothing happens. They appear frozen. They don't even seem to click. No error message. Nothing.
If I right-click the button in Design Mode and select Properties, I get sheet properties not the button properties. I can't seem to locate the command button properties any longer. I still see the button name "cmdButtonGetInfo" and "=EMBED("Forms.CommandButton.1","") in the name box and formula bar. The odd thing is if I create a new button it works fine until I save and close the file. When I reopen the file none of the buttons work.
It's like the buttons are being disabled when I close or open the file. Any suggestions?
Found the following code and it gets me close, but it copies the cell references, not the values. It also allows me to specify the file name from a cell reference.
I want the new workbook file to simply be saved, not opened, and a message box to display stating where the file was saved (will always be in the same location on the LAN).
What modifications do I make to this to get this to work per above requirements?
Sub CopyMe()
Dim SaveMeAs As String
SaveMeAs = Sheets("Sheet1").Range("B2").Text
Sheets("Sheet3").Copy
ActiveWorkbook.SaveAs Filename:="C:\My Documents\" & SaveMeAs
End Sub
My problem was that a userform defined with Excel at work (containing DT pickers) gave the message in the title when opening it at home. I had a light-bulb moment and wondered whether there was a difference in the version numbers for MSCOMCT2.OCX at work and at home. Turned out the work version was newer. I then copied the MSCOMCT2.* files from work, made a backup of them at home and copied those from work to my C-drive (Windows XP - c:\windows\system32\ ).
No luck. I then rebooted the machine - still no luck.
Then, finally I unregistered the old DLL via
Code:
regsvr32 /u c:\windows\system32\MSCOMCT2.OCX
(not sure if this was necessary, but I didn't think it could hurt). Reregistered the DLL via
Code:
regsvr32 c:\windows\system32\MSCOMCT2.OCX
and what do you know - it worked.
Summa summarum - it could be an idea to check whether the two machines have different version numbers for the MSCOMCT2.OCX files.
I'm new to macros and would appreciate any help this board can offer. Thanks...
between Excel workbooks. They can copy and paste between worksheets. When
you highlight the section to copy and then go to the new workbook both the
paste
and paste special are "grayed out". This is true whether you right-click the
mouse, go to the edit menu, or use control keys. This occurs with any data
type and the most simple workbooks. I have seen some suggestions here but
none have worked for this particular problem. I have reset the menus and
renamed the .xlb files and neither helps. You can open the clipboard and the
paste will work, but there is no paste special option. Any help would be
greatly appreciated. Thanks!
Currently, I import the file into one worksheet and cut/paste the rows manually into new worksheets/tabs. The files are very large, sometimes exceeding the 65,536 row limit, which I could avoid if the data was split out coming in.
Any help anyone could provide would be WONDERFUL. Thanks!
Code:
Sub test() Name "C:\myfolder\*.aqi" As "C:\myfolder\*.csv" End Sub
The problem is that VBA does not accept wild cards (at least as given here).
Any suggestions?
Thanks
abe