I have a word 2010 file with some hyperlinks to some Excel files and Excel cells. All these files are within a a single folder that I shall send to another pc. I don't know how to make the hyperlink directory path the same as the word file except for the file name.
View 1 RepliesIs there a way to generically reference a directory when creating a hyperlink? By this I mean, I have an XLS that documents the flow of a process. As an outcome of this process, text files are generated.
This process may be run multiple times. I would like to be able to create multiple directories: Run1, Run2, Run3, ...
I would like to put a copy of the XLS in each directory. I would like to put the output files from each run into their respective directory.
I would like to put hyperlinks in the XLS that point to the flat files. However, when I add a hyperlink it contains the full directory path. Something like C:MyDocumentsRun1output1.txt.
So, if I copy the XLS into directory Run2, the hyperlink is still going to point to a data file in directory Run1.
How can I generically default to look in the 'current' directory for a file? Or is there a different/better way around this? ............
is it possible if I have a directory like the following
[Code].....
with .csv files which are placed in arithmetic order 01,02,03 etc to determine if one .csv file is missing and print an error that this file is missing? I think that if I knew how many files I got I would create an array and check one by one the files.. But in case I have a dynamic directory and the number of files is not the same all the time , is this possible?
Cell C3 is validated to show a list of the worksheets in my workbook.
Is it possible to set up a hyperlink in cell B2 with the text 'Go To' which will pick up the selected sheet name from C3 to enable the user to go directly to that sheet?
I am trying to activate a Dynamic Hyperlink in IE. Each time a user starts a new session, a single parameter in the URL changes. Is there a way to search the page, identify the preferred link, and activate it without knowing the full hyperlink address?
The hyperlink is as follows: [url]
The parameter after "unitchange" is what changes from session to session.
I took a look around and noticed I can use the HYPERLINK command to link to a URL plus the contents of a different cell, such as something like =HYPERLINK(URL).
My question is, can I do a dynamic hyperlink that is smart enough to append whatever data is in the same cell to be the final part of the URL? Or do I HAVE to have another column where the hyperlink command is pointing to the first column as the variable?
For example I have a single column with either nothing in it or a 4 digit number. The 4 digit number is also the end of the URL I want it to link to. For example if the column says 1234, I would want it to go to [URL] .........
Im creating a template for a number of sheets and I want the hyperlink for my previous/next buttons (to move forward and back through sheets) to change based on cell values.
All my sheets are named as numbers ie '1','2','3' and so on
The name (and therefore number) of the sheet is held in cell E22 on each sheet.
The 'previous' button needs to therefore hyperlink to cell A1 on sheet (E22-1) and the 'Next' button to cell A1 on sheet (E22+1)
The 'buttons' I have though are arrows from Excels shapes and I dont know how to add code to them though to start the hyperlink.
Also if i wanted to reference sheet E22-1 etc in a HYPERLINK() function what is the syntax?
I am building a dashboard and would like to be able to have shapes I have created be clickable with a dynamic link to a document on our network. Something along the hyperlink() function.
In a cell, the function looks like this: =HYPERLINK(Worksheet!I34,Worksheet!F34).
The data in I34 changes, depending on the input from a drop down using a vlookup command. Is it possible to create a macro and assign it to a shape to make the shape a clickable link?
I am hitting a brick wall on this one. I am trying to assign hyperlinks to objects based on the cell value. Here is my code that colors the objects that have the same names as in Column AO. I am trying to also add a hyperlink to those same objects with the cell value in the list. The problem is while a hyperlink is added, I can not get the value of the cell to show up in the hyperlink address.
Sub TurnColOn()
Dim i As Integer
On Error Resume Next
LastRowa = Range("AO65536").End(xlUp).Row
For i = 2 To LastRowa
Labl1 = Range("AO" & i).Value
ActiveSheet.Shapes(Labl1).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1), Address:=Labl1
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Next i
End Sub
I have read a number of posts and other sites and can create the hyperlink in the desired cell but get an error message to the effect, "Cannot open the workbook."
The current macro follows. I believe its description is made in the comment statements below the date-author comment.
Sub DecomposeCTQ()
'Decompose CTQ Macro
'14Oct13 Charles T. Carroll'
'This macro copies the template to a new worksheet and takes data from the'
' active cell CTQ and makes it the parent CTQ on the new worksheet. Then'
' it renames the new worksheet with the active cell CTQ ID.'
If ActiveCell.Column 2 Then
MsgBox "You must be in the CTQ ID Column to run this program"
GoTo Leave
[code]......
I have created an employee schedule with 26 pay periods. Each pay period has its' own worksheet for scheduling employees.
To navigate to the worksheets I created a home page on one of the worksheets and would like to create an AutoShape for navigating with a hyperlink based on the pay period which is selected in cell B3. When the autoshape is clicked the user would go to the correct pay period worksheet.
For example Home Page:
Cell B3 contains drop down list of the 26 pay periods which the user selects the proper pay period (ie. 8Jun14-21Jun14)
Cell E3 is a cell reference on the sheet A1
Cell B6 contains hyperlink formula =HYPERLINK("#"&"'" & B3 & "'!" & E3)
Autoshape has formula =B6
When I click on the autoshape the hyperlink does not work.
I want to list file names from a directory and sub directories and hyperlink them. I have been able to list the file names onto an excel sheet, but I cannot get it to hyperlink to the file.
'Force the explicit delcaration of variables
Option Explicit
Sub ListFiles()
[Code]....
The script collects all the files in directory and sub-directories and list them in ascending format, I want them to get in transpose format. like for example: Root folder has many sub directories and in them a sub directory XYZ has 5 excel files, it will get the sub directory name in Col A and transpose all .xls files.
Col A | Col B________|Col C_________ |Col D___________|Col E________|
XYZ__|C:/root/test.xls|C:/root/Sales.xls |C:/root/Report.xls |C:/root/sam.xls|
[URL]
I am trying to prompt the user for the directory to be saved in and file name to be saved as; then save the workbook in the input directory with the inputted file name.
View 3 Replies View RelatedI 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
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 RelatedI 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.
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both
I would like to replace the blue bit of this Dynamic Named Range (DNR) with an INDIRECT formula in order to modify the starting point for the DNR: =OFFSET(DATA!$C$60,0,0,20,1). Unfortunately, I can’t seem to get my INDIRECT formula to work in order to use it to replace the blue bit above: INDIRECT("'"DATA"'!&ADDRESS(MATCH('SHEET1'!AC8,DATA_Date,0)+22,3)"). The orange bit of the formula above returns a value of 60, therefore the ADDRESS formula should return $C$60, that can then feed into the INDIRECT and act as the equivalent of DATA!$C$60. But it doesn’t.
View 2 Replies View RelatedI have a question on the above but can't seem to find a solution. There are two ways that I can find for dealing with dynamic lists via data validation:-
1) Offset and match, cavet being the data must be sorted a-z
2) Have lists for each potential selection
Is there any way to get around 1 without having to do 2? E.g. Got two columns of data, unsorted, and a list from which the user can choose from. The user chooses from the list in first cell, in the second cell require the dynamic list to return all the values accordingly?
I have a dynamic table which is linked to a couple of charts. The table must remain dynamic.
I needed to add two new columns to the table, "Focus Area" and "Category". I need a drop down list in the "Category" column to be dependent on the item selected from a drop down menu in "Focus Area".
I can get the first row of the dynamic table to do this.... however; subsequent rows all lock the drop down list in the "Category" column to the same choices regardless of what is chosen in the "Focus Area" list.
Is there a way to make dynamic drop downs within a dynamic table?
The command Application.GetOpenFilename will open a explorer to be able to choose a file, what is the command to open a explorer but only to choose a directory?. Is there such a command?.
View 7 Replies View RelatedThe Kill function works OK but the ThisWorkbook.SaveAs part does not work for some reason.
Please note that there are 2-spaces between the words: P3 CATS
For a macro i'm writing I need to search for a directory.
Users enter a projectnumber, this is being used for a search string.
But how can I search for folders/directories in Windows?
I've got the startfolder: 'C:Projects'
Then the project which the user is searching for can be in several subdirectories.
1. Is it possible to save additional worksheets (right now it is only copying one worksheet)?
2. How can I hard-code the directory path so the file goes to that one locatin (i.e. the "master directory")?
Also, is it possible to save userforms out of this workbook (i.e. some of the userforms contain information that is useful...it would be great to have them in the "output" file that gets saved to the "master directory"). Thanks!
Private Sub SAPfile_Click()
myfile = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xls), *.xls")
Worksheets("Output-SAP Plan").Copy
ActiveWorkbook.SaveAs myfile
ActiveWorkbook.Close
MsgBox "SAP file created."
End Sub
I've been looking for code to print Excel files from a directory with multiple worksheets. I found something close on this forum, but it prints 2 copies which is fine for pdf, but not for hard copy. I would like just 1 copy.
Sub PrintPDF_PLOTDIR()
Dim PATH As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet
Application.EnableEvents = False
Application. ScreenUpdating = False
PATH = "C:DATA\_PLOTDIR"
FileName = Dir(PATH & "*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=PATH & "" & FileName)
Application.ActivePrinter = "Adobe PDF on Ne00:"
For Each WS In Wkb.Worksheets
ActiveWorkbook.printout Copies:=1, Collate:=True
Next WS
Wkb.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Imagine this:
* Main Category #1
Sub Title #1: "Value"
Sub Title #2: "Value"
Sub Title #3: "Value"
Sub Title #4: "Value"
* Main Category #2
Sub Title #1: "Value"
Sub Title #2: "Value"
Sub Title #3: "Value"
Sub Title #4: "Value"
Sub Title #5: "Value"
Sub Title #6: "Value"
Sub Title #7: "Value"
Sub Title #8: "Value"
Sub Title #9: "Value".................
Main categories and subtitles are in the same column. "Value"s are in the column to the right.
I want to make a list in the columns to the right. This list will only show something if the "value" is not zero. It will show in the first column right from the original list, the main category to which the subtitle belongs to and the subtitle. Something like this (imagine subtitle #2 from cat #1 and #2 and #3 from cat #2 are not zero):
I want to have a excel file with VBA code and it should operate like the following:
It should check the subdirectory's in a certain directory and put this on a excel sheet.
Every time when you start the sheet it should compare the sheet with the sub directory's and make al the changes in the list.
In the end the excel sheet should be a representation of the directory.
I can't get it to work when something is changed or a directory is added or deleted.
[Code] ....
An already existing file will be changed by my macro and at the end I want the user to save the file with a different name in a different directory with the option to change the filename or directory. Therefore I use the excel-standard-dialog "save as". But I want the dialog to start in another path as the path the original-file is stored in. But also I use the ChDrive and ChDir Parameters the dialog even starts in the original-file-path. I think this is standard for the dialog and I should maybe use a parameter in the show-arguement? The code is below.
Option Explicit
Sub SaveMyFile()
Dim xFileName, xAnswer
ChDrive "J"
ChDir "J:myfolder"
xFileName = "testme.xls"
xAnswer = Application.Dialogs(xlDialogSaveAs).Show(xFileName)
End Sub
I had posted earlier and felt I had solved my problem, but I have not. What I am trying to do is use code to change directory when the save menu is activated.
I want to be able to open from one location and then it defaults to another. I have tried "chdir" , but I can't seem to get it to work. Even when I change the DefaultFilePath, the "save as" location defaults to the location where the file was opened from. I want to default all "saves" in a specific location without using the menu to select.