Hyperlink Using Current File Name

Jun 23, 2008

I have an Excel 2003 application that creates a new workbook and adds hyperlinks that link up to other tabs withing the same new workbook.

Issue is that if say the default workbook name was "Book1" but the user saves this workbook with any other name, the hyperlinks die.

Is there anyway to use the current name of the file (instead of "Book1" in this example) in the formula "=HYPERLINK("[book1.xls]Sheet3!A1", "test")"

View 9 Replies


ADVERTISEMENT

Save Excel File As Another File Using Current Date As Part Of File Name

Oct 10, 2011

VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.

View 1 Replies View Related

Saving Cell Contents To XML File Saved As Current Workbook File Name

Dec 12, 2012

I have numerous spreadsheets that I need to open and unhide a sheet, that has XML data stored in cell A1. What I need to do is copy that data in cell A1 and paste it into a text document and save that as an XML file saved as the XLS workbook name with a date stamp.

I'm running into many issues, the main issue is the saving as current file name, and the formatting of the text/XML file.

Here is my current code, which doesn't reference the current file name and is just very generic. Once I get the saving as file-name correct and the formatting of the xml file correct, I will work on it a bit more.

Code:
Sub Test()
Dim Rng As Range
Dim wb As Workbook
Set Rng = Range("A1:A2")
Set wb = Workbooks.Add
With wb
Rng.Copy

[code]....

View 4 Replies View Related

Open A Closed File To Archive Some Data From A Current File.

Oct 20, 2009

I have the need to be able to open a closed file to archive some data from a
current file.

what coding i need to be able to kick the "open file" bit off?

View 4 Replies View Related

Changing Reference From Current Years File Data To Blank File Data

Feb 19, 2014

I have a blank file and a data file for current year with formulas.

So I am trying to take a couple of tabs from current year data file and pasting them into the blank file.

My question when is do that all the formulas get referenced to the current years file. i want to keep the same formulas but reference the tabs on blank file.

Is there a quicker way to do this rather and going into all the cells and changing reference from current years files data to blank files data.

View 3 Replies View Related

Use String As File-hyperlink?

Jan 27, 2014

I want to add hyperlinks to certain cells using drag and drop. I've got a userform with a toolbar that I use as a drop zone for files (called tbDrop). In the userform, below the dropzone is a label called "label2"

The dragging and dropping works as a charm, label2 shows me the file location and adds it as a hyperlink to the cell.

However, when I click the hyperlink the file can't be opened.

I've used the result string, added it manually as a hyperlink and recorded it. That worked, but i saw no difference in the recorded macro.

VB:
Private Sub tbDrop_OLEDragDrop(Data As MSComctlLib.DataObject, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)
Label2.Caption = ""
Dim a As String

[Code]....

View 3 Replies View Related

Insert A Hyperlink To Another File

Nov 12, 2008

I'm trying to insert a hyperlink to another excel file such that when you click on the link it opens that file up... When i go to insert a hyperlink and i select the file.. when i click on the file it says "the adress of this site is not valid. Check the address and try again" The file is on another drive on the server and i was wondering what the restrictions are in terms of where the files have to be in relativity to each other

View 4 Replies View Related

Hyperlink To PDF File From Excel?

Nov 17, 2012

I created a hyperlink to a .PDF and it works when I test it. However, when I save the file, go in later, and select the hyper link I am met with a 'Cannot open the specified file' message.

I have had this prior in Excel. The hyperlink works fine in Outlook, Word, and Access...

View 2 Replies View Related

Attaching File To Hyperlink

Jun 10, 2008

When you click on Cell C5 (which will say "ADD") I would Like the (Computer Browse Menu) to appear
you can then search for the file and press Attach

This will then create a hyperlink in that cell (or in a different cell) Saying "File Attached"
when you click on "File Attached" it will then open that file

I completly lost on how to do this
but here is the formula i have so far when it comes to click on the cell

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim arrValues
If Target.Count > 1 Then Exit Sub
If Intersect(Range("C5:C5000"), Target) Is Nothing Then Exit Sub
End Sub

View 9 Replies View Related

Hyperlink Function To Another File

Jan 14, 2008

why no formula based hyperlinks will work in my workbook. even the simplest formula's. either it returns "cannot open specified file" or it does absolutely nothing even though the cursor acknowledges its a hyperlink.

View 5 Replies View Related

Using Current File Name And Sheet Name?

Mar 8, 2014

for someone that might have files they need to process named the same with an index. How can I set this up so when they open the file to run the macro to grab that specific filename they opened and the sheet name? I need to set the filename to a string and the sheetname to string but I dont know how to get that to work.

example...
filename: download.csv, sheetname: download
filename: download (1).csv, sheetname: download (1)
filename: download (2).csv, sheetname: download (2)

Code:
Set wb2 = Workbooks("DOWNLOAD.CSV")
cnt = wb2.Sheets("download").Cells.SpecialCells(xlCellTypeLastCell).Row
strReportCount = (CStr(cnt))

View 1 Replies View Related

Hyperlink To File Based On Cell Value?

Sep 29, 2012

Basically I have a folder with file names in the format:
"XXXXX characterstring"
where X is a digit

E.g.:
45627 -jdsuuuds.pdf
93823 Coates.pdf
98221 dagggiisa d sass.pdf

And I have an excel sheet with a list of the numbers at the start of these file names, eg:
45627
93823
98221

So what I want to do is have each cell link to the file with the corresponding number. So I click "45627" and it links to "C:path45627 -jdsuuuds.pdf"

I could do it if the file names matched exactly to the cell value, but when they don't.

It's super easy when the file name matches exactly:

VB: ActiveCell.Hyperlinks.Add ActiveCell, "C:path" & ActiveCell & ".pdf"

VB:
Sub makelinks() ChDrive "c"
ChDir "c:pathl"

For Each ce In Selection
filee = Dir(ce.Value & "*.pdf")

[Code]....

View 2 Replies View Related

Hyperlink To Another Cell Of Another Excel File

Aug 12, 2009

Is it possible to do hyperlink to a specific cell in one of the tabs of another excel file? I managed to link to another excel but can't make it go to a particular cell.

View 11 Replies View Related

Hyperlink - Update When File Moved?

Jul 20, 2013

I have a spreadsheet with hyperlinks to various file types (PDFs, Docs, etc.). Is there any way that I can have the hyperlinks update themselves automatically when I move one of those files to another location?

View 2 Replies View Related

Create A Shortcut To A File, Not Hyperlink

Jul 5, 2006

How can I change the text color within a cell when I am using a concatenation formula? I have the following formula;

=" Total Bonus Earned ("&TEXT(A2/VLOOKUP($A$1,PR_DB,2,False),"#0.0%")&" of 2006 Earnings):"

In the text function portion I would like to format the result in red. An example of the result of my formula I am seeking is as follows; Total Bonus Earned (10% of 2006 Earnings): Do I need to create a custom format and if so how do I call it up within the text function?

View 2 Replies View Related

Hyperlink File From User Form

Jul 10, 2006

I have a excel Product database and a Data Entry Userform. I can gte this to work fine but need to make some improvements to make it look professional.....here's what I am looking to do...:

1) When a user add a new product using the Data Entry form, I want the user to be able to add a hyperlink to the datasheet of that product and this hyperlink will then be saved in my database in column M of that product alongwith all other details in other columns. The DataSheet files are PDF files that are stored on the public drive. So I am hoping to use a browse function or similar to select the file to hyperlink to...

My current hyperlinks look like this which I have created manually directly into the column M (not through userform):

V:PublicCOMBData SheetsData SheetsXXXXX.pdf.............

View 3 Replies View Related

Hyperlink Formula Using Cell As File Name

Feb 15, 2008

I have the following formula =HYPERLINK(("G:ProjectTest Project"),"Link". Now this works fine, However I have a cell "A5" containing "Test Project" And I would rather the link refernced the cell contents So the code would look something like this; =HYPERLINK(("G:Project"+A5+"),"Link"). or something to that effect, this obviously doesnt work hence why I am here.

View 3 Replies View Related

Hyperlink To Another File On Another Drive Fails

Jun 13, 2008

I made an excel sheet that links to other sheets on a different drive. It works while I am making it, but upon save and reopen all the hyperlinks produce this error: The address of this site is not valid. Check the address and try again.

Auto Merged Post Until 24 Hrs Passes;Figured it out. File, Properties, Summary tab, Hyperlink Box to base Drive. Auto Merged Post Until 24 Hrs Passes;Figured it out. File, Properties, Summary tab, Hyperlink Box to base Drive. Auto Merged Post Until 24 Hrs Passes;Figured it out. File, Properties, Summary tab, Hyperlink Box to base Drive. Figured it out. File, Properties, Summary tab, Hyperlink Box to base Drive.

View 2 Replies View Related

Trying To Import File Into Current Workbook

Jul 21, 2008

Sub Macro1()

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "COOPtbl1", "C:Documents and SettingskanegrMy DocumentsBook1.xls", True, ""

End Sub

Heres the code I keep getting a

"Run-time error '424':

Object required"

Message.

View 9 Replies View Related

Save File To Current Folder

Apr 23, 2009

I'm running a macro that intially opens an input box where you specify the filepath and name of a .ped file to import into the macro. The default path of H:BOM2.ped may not always be the path I use. It may be just H:.ped or H:Bom1.ped.

HTML INITIAL CHECK
Response = MsgBox(".............do you want to import BOM data?", vbYesNo)
Do Until Response = vbNo
'SELECT RAW FILE
Dim Message, Title, Default, MyValue
Message = "Please Enter PathFile Name for Source File"
Title = "Create CHINA INSPECTION REPORT(s)" ' Set Title.
Default = "H:BOM2.ped" ' Set Default.......

View 9 Replies View Related

Saving File As Current Workbook Name

Apr 10, 2007

ChDir "C:Documents and SettingssysxxxDesktopcode testNew Folder"
ActiveWorkbook. SaveAs Filename:= _
"C:Documents and SettingssysxxxDesktopcode testNew FolderBillsFileName.xlw" _
, FileFormat:=xlExcel4Workbook, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Here's the code. My only question is how would I change "BillsFileName.xlw" to be the name of the current workbook?

Is it something as simple as

New Folderactiveworkbook.name.xlw

View 3 Replies View Related

Show Userform Only If Hyperlink Does Not Locate File?

Dec 26, 2013

On sheet4 is a list of dates and I named that range as UPDATES2013. Each cell in that range is a hyperlink. Sheet1 cell I3 is a data validation list with the named range UPDATES2013 as the list. When a selection from Cell I3 is made I can get it to follow they hyperlink with the code I have. The problem I am having is if there is no file found, then I would like my Userform1 to show. I have tried several different things with the code, but every time I think Im going to get it to work....it doesn't. It either wont show the Userform at all, or it shows it even if the file is found. I am currently putting the code to run the macro on the worksheet module using the worksheet_change event.

One last thing...I also have hyperlinks throughout Sheet1 that will lead to the same files as in the named range UPDATES2013 from sheet4. I need the same thing to happen if they click one of these hyperlinks instead of using the data validation list in Cell I3. Is there a way to make one code that will show Useform1 if a hyperlink is selected and the file cannot be located (whether it be by the individual cells on sheet1...or the data validation list in Cell I3)?Here is the code I am using right now.

VB:
Private Sub Worksheet_Change(ByVal Target As Range)
DoFollow2013 ActiveCell
End Sub [code]....

View 9 Replies View Related

Search Path & Create Hyperlink To File

Oct 1, 2009

I'm trying to add code that create a hyperlink to a file in a recently created folder in the path of L:Elec Dept ProjectsRELEASED FOR CONSTRUCTION". The code below should find the newly created *BOM*.xls created and created a hyperlink in the next available row starting on row 27 and column O on my worksheet "BOM". The code currently runs with no errors, but I'm not seeing any resulting text with path / links created.

View 5 Replies View Related

VBA To Mail Merge And Attach Hyperlink File

Mar 30, 2012

I have an Excel file that has a persons name in column A, company name in column B, email address in column C and a hyperlink to a specific file to be sent with the email in column D.

I have the code to send the email and it works fine.

I've searched the net for mail merge and hyperlinks and have only become confused.

Here is what I want to do, attach the mail merge created file to the email, then the file associated with the hyperlink, I want to attach the actual file from the hyperlink into the email.

View 1 Replies View Related

Hyperlink Does Not Maintain Path To Target File

Sep 19, 2007

I read about Hyperlinks in the August 2006 "OZGRID" Newsletters but found no answer.

Assume 2 workbooks - book1 and book2, both located in "c:" (root of drive C)

Now, In book1 I have declared an HL to book2 using CTRL+K and upon clicking in that cell the book2 is opened.

After MOVING(!) book1 to a new location (D:DATA) and clicking the HL - I get an Error "cannot open the specified file".

While book1 was still in root C: - I tried to force to c:ook2.xls by typing it in the HL address window but after saving, Excel deletes the "c:" and leaves "book2.xls" only – I think because both files were located I the same directory.

I am aware that my "problem" can be solved by using the function HYPERLINK – but I would like to learn (if possible) how to insert an HL using CTRL+K that will maintain the path although moving the file which contains the HL).

View 9 Replies View Related

Save File Under Current Name In A Selected Location

May 4, 2006

I get an xl file sent to me every day which I upload into a database. I then save the file keeping its name and save it into a selected folder. Is it possible to add a macro at the beginning of my current macro that saves the file using its name to a selected folder?

View 4 Replies View Related

Excel Macro That Prompts To Choose File To Hyperlink

Jun 12, 2014

I would like to write a macro that would insert a hyperlink to another file an would prompt the user to select the location of the file once the macro was run. I am not sure how I would get my macro to prompt the user to define the location (which is located on a network drive).

View 7 Replies View Related

Excel 2010 :: Hyperlink With IF Statement - Cannot Open Specified File

Aug 30, 2012

I Have a CheckBox Thats Linked with CA22

In Cell CB22 I have the Following Formula:

=IF(CA22=TRUE,"1","")

In Cell CC22 I Have...

=IF(CB22="1","RM-4E ADD1",HYPERLINK('RM-4E (Add - 1) (2)'!A1,"RM-4E ADD1"))

The Sheet namne its supposed to Hyperlink to is called

RM-4E (Add - 1) (2)

When the Check Box is Unchecked it Changes CA22 to False and Puts in CC22 Text: RM-4E ADD1

When the Check Box is Checked it CHanges CA22 to TRUE and is supposed to create a Hyperlink to that sheet...Only when i click it it comes back with

'Cannot open the specified file."

I even went as far as typing out the Word Hyperlink( clicked the sheet and selected Cell A1 on that sheet but it still comes back with the error!

Excel 2010

View 9 Replies View Related

Excel 2003 :: HYPERLINK To Open PDF File Reader

Feb 27, 2013

How do I create a hyperlink in an Excel 2003 file that will open a PDF file with Reader?

I have an Excel file of hundreds of Customer names and invoice numbers. I have a directory of PDF files. The name of each PDF file is the customer name, a space, and the invoice number. I should be able to have a column in the Excel file that contains a formula that creates a hyperlink to open the corresponding PDF file.

I used the HYPERLINK function and built the path and file by concatenating information. When I click the hyperlink, I keep getting an Excel error: Cannot open the specified file.

Do you think this problem is something of my own doing or some "undocumented feature" in Excel?

View 1 Replies View Related

Displaying The Current Opened File's Directory Path?

Sep 25, 2008

What is the easiest way to display in a cell the opened file's directory path where the file is stored? I would like to use some sort of formula (instead of the VB route) but I don't know if such a thing exists.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved