Hyperlinking With Address And File As Variables

Mar 16, 2009

I'm trying to Hyperlink my documents to a folder and file name specified by different cells in my workbook

Or essentially, I'm trying to go from this...

View 14 Replies


ADVERTISEMENT

Variables As Part Of File Address

Mar 21, 2007

I've been trying to use combo boxes to open, then copy and paste information from specific worksheets the user requests. So far, my approach has been unsuccessful --and the suggestion to simply use the GetOpenFiles procedure was helpful but failed to address more than the "open files" part of my application. Can variables be part of an excel file address as I've tried to use them in the "strFile =" statement immediately below?

Public strFile As String
Public strSheet As String
Public strSheetName As String

strFile = "D:Documents and SettingsmccaralDesktopMP2 MONTHLY FINANCIALS_
15_FINANCIALS" _ & strSubtask + "-" + strSubelement + "2007" + strMonth + ".xls"
strSheet = "Task Order" & strSubtask & "-" & strSubelement
strSheetName = strSubtask & "-" & strSubelement

Workbooks.Open Filename:=strFile
Sheets(strSheet).Select Range("A1:P15").Select
Selection.Copy...................................

View 2 Replies View Related

Hyperlinking Directly To A Specific File?

Jul 6, 2009

I'm having trouble creating a hyperlink that will automatically take a value from a cell and link me directly to a file of the same name across the company server.

For example in column B I have a number along the lines of

43FG2345678

In a folder on the server I have a word document that goes by that exact file. I need to create a hyperlink that looks up that value and searches for that specific document in the location file, and then directly open that file.

The value in column B will change with each line so as I copy the formula/hyperlink down I want it to automatically go in and replace the text and then lookup the file.

I have tried the concatenate function within the hyperlink but not had much luck.

View 2 Replies View Related

Two Variables To Define Cell Address

Dec 13, 2006

I have 3 variables right now...

Dim celadr As Range
Dim cLetter As String
Dim avgRow As Integer

and one line of code reads:

celadr = Range(ColumnLetter(LastScoreColumn) & avgRow)

When I mouseover columnletter it shows "H" and when i mouseover avgRow it shows "2"... this info is right but I think the problem may be trying to combine a string and an integer value as a range. How can I do this so that celadr is the range H2?

View 13 Replies View Related

Hyperlinking To PDF Documents

Apr 28, 2009

This code will take the value of the active cell (which is a filename) and open the corresponding PDF document of the same name.

Sub OpenPDF()

Dim fName As String
Dim fExt As String
Dim fPath As String
Dim fFullPath As String
fName = ActiveCell.Value
fPath = "M:Books"
fExt = ".pdf"
fFullPath = fPath & fName & fExt
ActiveWorkbook.FollowHyperlink Address:=fFullPath, NewWindow:=True............

View 9 Replies View Related

File Name With Variables

May 8, 2009

I'm getting an error here. Yr and State are variables defined by an inputbox. Is this not the proper way to write out a filename with variables in the mix?

Data Source= "P:Current Quarter" & Yr & "Files" & State & Yr & ".mdb"

View 9 Replies View Related

Hyperlinking Files With A Portion Of Name

Apr 15, 2014

I have a few thousand files that I'm trying to hyperlink into excel. The files are named with a date at the end of it. I want to be able to hyperlink to that file just by looking up the name without the date portion. So basically I'm asking if I could hyperlink a file with only a part of the name, and yes the names are distinct even without the date. I now am loading all of the file names into a spread sheet via a VBA code, however with almost 10k files that process takes longer than I'd like.

View 7 Replies View Related

Absolute Hyperlinking Within Worksheet

Jul 14, 2014

In trying to make an alphabetized record of all inventory for work, I ran into a snafu when creating shortcuts for other users to the different letters.

I hyperlinked something like a table of contents together and hyperlinked them to the cells, so clicking on B in the table of contents takes you to cell B64. I want to future-proof it with absolute references but haven't been able to.

I tried right clicking and typing in the cell name--but when I add more cells, instead of moving to B72 like it should, the hyperlink stays in B64, which is now part of the As.

I also tried: =HYPERLINK(A$64,"B") as well as =HYPERLINK(B_Start,"B") by naming the cell. The name went to B72, but the link stayed in B64

View 3 Replies View Related

Hyperlinking To Specific Number On URL

Sep 6, 2013

My URL is a javascript page. which looks something like this:

HTML Code: 

var quantity_in_stock_S = 2
var quantity_in_stock_M = 2
var quantity_in_stock_L = 1

and I want to hyperlink the numbers to individual excel cells

How can I do this?

View 1 Replies View Related

Hyperlinking Info From Another Computer

Jul 1, 2006

In my restaurant we have a master computer upstairs that collects all of the register sales info. Then we have a computer downstairs that my employees sign in and out on.

This excel timesheet keeps track of the amount of labor hours being used per day. Is there a way of doing this without putting the timesheet on a web page or putting my sales info on a web page?

View 4 Replies View Related

To Get Files Directory (Address)

Dec 7, 2008

If I know the name of the file how can I get that files directory (Address) with macro or function?

View 9 Replies View Related

Hyperlinking Texts In Hierarchy Chart?

Jul 2, 2013

I have created a hierarchy chart using smartart. Is there a way to hyperlink the texts in each box to other sheets in the workbook?

[URL]

View 3 Replies View Related

Web Address Needed For Excel File

Apr 25, 2013

I want to create a web address for the attached file and then post the link on my football forum so that members can view the file in their browsers. Is it possible to do this?

Attached File : Book1.xlsx‎

View 2 Replies View Related

How To Get File Address And Paste Into Particular Cell

Sep 29, 2011

I would like to be able to open a dialog box, have the user select the file of interest, and then paste the file address into a particular cell.

I have the macro started, I just don't know the correct language to get the file address. How to get that dialogue box to appear and select the file.

Code:
Sub PDF_SETUP()

If Range("Z40").Value = "" Then

Dim mypath As String
Dim myfile As String

mypath = ThisWorkbook.Path

'Display dialog box to get file address.

[Code] .........

View 9 Replies View Related

Hyperlinking Text (paste Special On Some Links)

Feb 26, 2008

I have a problem in that in using the paste special on some links I created using HYPERLINK/VLOOKUP formulas the hyperlinks themselves have gone (ie nothing happens if you click on the data).

What I want is a macro that can turn a cell's contents into a hyperlink (i.e the location and friendly name are the same) by means of a simple keyboard stroke. I tried doing this using the macro recorder, but it didn't recognize when I was trying to paste the text into the Insert Hyperlink address bar and it just recalled the name of the address in the VBE window as if I'd written it in manually.

View 9 Replies View Related

Problem Copying File Address To Textfield

Feb 22, 2009

i made a macro that uses a form with a "browse" button to open a text file and put every word in this textfile in a single cell but i had a problem when copying the address of the file to a textfield, here's the code :

View 5 Replies View Related

Working With Text (Sorting And Address File)

Apr 3, 2007

I have a list of about 300 addresses that I'd like to be able to put in a sortable state. In column A1-A4 is the following:

Name
Street Address
City, State Zip
Email Address

Then there is a blank row and then the next address. What I would like to do is figure out a way to take the info in columns A1-A4 and move them horizontally to A1, B1, C1, D1, respectively. Sure, I could cut and paste, but it would take me forever. I tried a macro,

View 9 Replies View Related

Open File Where Path, Name & Sheet Name Are Variables

Jan 18, 2008

I'm trying to use vba open a new workbook and then edit it. But i can not select cell in the new workbook. but it can be activated!

it can open the file (fname) and activate the sheets (sName), but will flage an error on range select.


Workbooks.Open fileName:=fPath & "" & fName
Workbooks(fName).Sheets(sName).Activate
Range("A8").Select

View 9 Replies View Related

Hyperlinking Multiple Cells Based On Another Cell Contents

Feb 23, 2012

I have just started to learn how to use vba in microsoft excel. Over the past few days i have been creating a drawing register and have incorporated multiple routines based on searching this forum. Up until this point i have foud everything i needed on this forum, but now i cannot work out how to creat a routine that does the below.

The setup
*I have a folder called Zircon Plant
*That folder contains 3 folders 01. Superseded, 02. PDFs and 03. Documentation (the 01. Sup...etc. is the actual folder name, the 01, 02 and 03 isnt distinguishing the 3 folders)
*The Register is located in 03. Documentation
*The PDFs i want to hyperlink to are located in 02. PDFs

What i want to do
*i have 2000 rows of drawing names
*the drawing name in excel is spread over 5 side by side cells on each row (rows 21 - 2020) so for example the first row the information is in cells (H21, I21, J21, K21 ,L21)
*the cells i want to contain hyperlinks are V21 - V2020

so what i want is when excel opens up (i already have another script running at startup so i will be placing this after the other startup routine i have) i want it to search in folder 02. PDFs using the contents in (for example the first row (21) and colums HIJKL contain (H21)4CP(I21)-(J21)D(K21)-(L21)55000) each row and combine those cell contents to find the file name 4CP-D-55000.pdf. I then want it to go down every row until row 2020 untill it has added all pdfs.

(The second row is (H22)4CP(I221)-(J221)D(K221)-(L21)55001.....file name would be 4CP-D-55001.....third row would be 4CP-D-55002 etc...)

If such a pdf with that name doesnt exist, i want the cell (for example v21) to read "PDF not available"

based on a search here i have been able to do this for 1 cell, but dont want to do 2000 seperate routines. the current routine i have for it to work on one cell is

' Select PDF range
ActiveSheet.Range("u21").Select
' Promt PDF Hyperlinking
ActiveCell.Hyperlinks.Add ActiveCell, "I:DraftingAs Built4CP - PinkenbaE - ElectricallZircon Plant02. PDFs4CP-D-55000.pdf"

I also have multiple sheets. the first sheet is D - Documentation. I will eventually copy this routine 4 more times as i will be able to work out how to apply it to the other sheets

View 5 Replies View Related

Get Full Address Of External File By Specifying Filename And Then Evaluate

Jan 9, 2014

I'm really looking for a way to get the full address of the external file by specifying the file name in a cell and then evaluate the formula. For example,

[Code] .....

is the full address of the source file called 'Source'. My idea is: if I just type the file name 'Source' in a workbook's cell $A$1, and in cell $B$1 I want to make the formula something like:

="C:Users["&$A$1&".xlsx]Sheet1'!$F$1"

This will make my project very impressive with this feature: a user just types the file name to establish the link and evaluate the formula for results.

View 4 Replies View Related

Open File With Inputbox Result In The Middle Of The Filename Address

May 26, 2007

I am trying to open a workbook with

Workbook.Open filename:="xxxxx"

The address in the xxxx is something like this "G:AnimalMammalZZZZGorillayyyy.xls"

The ZZZZZ portion is the result of an InputBox.

What is the syntax for placing the result of that InputBox in the middle of the address?

View 3 Replies View Related

Using Macro To Find Email Address In Address Book Of Outlook

May 22, 2014

I am struggling to find a macro which can look at a name in column 'BT' and search it in the address book of Outlook to then place the email address of that person in column 'ED'

There are 35,000+ people in the address book and there may be over 5 email addresses for one name, so is there any way a message can appear for the user to select which email address is correct if there is more than 1 contact for that name?

View 1 Replies View Related

Need To Pull Street Address Out Of Full Address In A Cell

May 20, 2014

Assume the following list of addresses are all in separate cells of a single column (A1-A4). I just need the formula to extract the street addresses, and then a separate formula to extract the zip codes.

5430-44 PASCHALL AVENUE PHILADELPHIA, PA 19143 OPA/BRT#: 884350845

4010 MARPLE STREET PHILADELPHIA, PA 19136 OPA/BRT#: 651087200

2618 SOUTH HOWARD STREET PHILADELPHIA, PA 19148 OPA/BRT#: 391251216

5737 WOODCREST AVENUE PHILADELPHIA, PA 19131 OPA/BRT#: 522155600

View 2 Replies View Related

Split Address Which Is In Single Column Which Contains First / Last Name And Address Without Any Comma?

Apr 24, 2013

I have a list for addresses in excel in single column as shown below - aanandhi narayanan 3430 chemin de riviere sanjose,CA95148

DOMINIC ABANO 3838 GLENGROVE WAY SAN JOSE, CA 95121

abdi abdi 5390 monterey rd #6 sanjose,CA95111

Sheribel Abinsay 3212 Gateland CT San Jose, Ca 95148

I need the result to be in a way like -

3430 Chemin de riviere
San Jose
CA
95148

3838 Glengrove way
San Jose
CA
95121

5390 monterey rd#6
San Jose
CA
95111

3212 Gateland CT
San Jose
CA
95148

I have around 12000 records with the same format.

View 9 Replies View Related

Get Email Address From Global Address Book In Outlook

Feb 25, 2014

On a worksheet called "Contact Info" column A starting in row 2 I have a list of names (variable length). In Columns B2-D I need the email address, work phone number, and cell phone number.

View 5 Replies View Related

Unzip Code - Works Without Variables, Breaks With Variables...

Feb 5, 2009

Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...

I have the

View 2 Replies View Related

Function Back Variables: Function Give Two Or More Output Variables

Jul 27, 2006

Can a Function give two or more output variables. e.g.

Sub a()
x = 5
result = Y(x)
End Sub

Function Y (x As Integer) As Integer
Dim B
B = ... * x
Y = ... * B

this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?
I need this because function works with large matrix and I want to extract some values appeared in between.

View 2 Replies View Related

Defining Variables Using Variables

Apr 27, 2006

I'm trying to loop through a range in excel from access, checking where the titles (in Excel row 1) match with the fields (in a recordset in Access that is passed to the function) - and where they do, I want to dimension a variable to hold the column number - I'm not sure it's possible, but I'd be interested to know either way. The line I'm asking about is at the bottom of the code - the rest of the code is just to give context...

Sub ImportGeneric(rsImported As ADODB.Recordset, rsConfirmed As ADODB.Recordset)
Dim fd As FileDialog
Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Worksheet
Dim iFilePicked As Integer
Dim strFilePath As String
fd.Filters.clear
fd.Filters.Add "Excel files", "*.xls"
fd.ButtonName = "Select"
iFilePicked = fd.Show
If iFilePicked = -1 Then
strFilePath = fd.SelectedItems(1)
Else ..................

View 3 Replies View Related

Erase Variables All The Variables Or To Set The Value Of Them To "0"

Jan 16, 2007

i have a "problem" to empty / reset my variables. I defined them as vHour1_KW2 where the "1" is from 1 to 21 and the "2" starts from 1 to 53. Now I want to erase all of this variables or to set the value of them to "0".
At moment I use following

vHour1_KW1 = 0
vHour1_KW2 = 0
...
vHour1_KW53 = 0

vHour2_KW1 = 0
vHour2_KW2 = 0
...
vHour2_KW53 = 0

until...............................

View 3 Replies View Related

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







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