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).
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.
i am needing to issue a dos command in excel? basically i need to send a target link to a file. i cant use a hyper link for several reasons, and this is the only way i know how to go about this.
I have a variable ("DestFile") that defines a path to a file (used in saving the file)...
I'm in the process of getting a Sub to hyperlink to this file, but in some circumstances, I may only want to hyperlink to the folder, not the actual file...
How would I go about trimming the "DestFile" address to get a "DestFldr" address?...
An example of "DestFile" might be; S:BryanFor KenGulf ConstructionST0609014-t.xls (the file name length may vary) What code can I use to consistently trim it back to; S:BryanFor KenGulf Construction as the "DestFldr" variable?
I have created a list of projects which have hyperlinks. When the hyperlink is clicked, I run an excel macro to display the project details in a pdf. However, I am having a problem knowing which project is selected so I can get the correct project details.
My list is in columns B and C.
Project # Select Project
9 Project 9
14 Project 14
21 Project 21
I create the hyperlinks here:
For i = 7 To 96 If wshS.Cells(i, 10).Value = "Y" Then ' Copy cell values wshT.Cells(j, 2).Value = wshS.Cells(i, 1).Value wshT.Cells(j, 3).Value = wshS.Cells(i, 2).Value ActiveSheet.Hyperlinks.Add Anchor:=wshT.Cells(j, 3), Address:="", SubAddress:=wshT.Cells(j, 3).Address, TextToDisplay:=wshT.Cells(j, 3).Text j = j + 1 End If Next i
When the hyperlink is clicked, I need to find the project # to retrieve the project information. The project number is in the cell to the left of the hyperlink. I want to place that value into PrintFilter.
Currently this line: PrintFilter = wshT.Cells(Target.Row, 2).Value receives the message, error 438 object doesn't support this property or method.
If I place the number in PrintFilter, the macro runs successfully. That is the PrintFilter = 9 below.
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim PrintFilter As Integer Dim wshT As Worksheet Dim wshTemp As Worksheet Dim wshTemp2 As Worksheet
I am trying to create a Karaoke Song List in excel, which would show four columns on each page with Artist, Track, Artist, Track. I am importing a text file to do this. The problem I have is that I can import the file so that it puts Artist and Track going down, however, it would then need to go back to the top of the page to fill in the right hand side of the current page. Is there any code that retrieves the current print page number of the current cell (so that I can initiate the code to go to the top right of the current page and continue importing)? Or is there a better way of doing this?
When inserting a hyperlink in an Excel cell, the default pathname for the new link is always the current pathname of the workbook. After navigating to a distant pathname and inserting a hyperlink, inserting the next hyperlink in the next cell starts all over again at the current path.
Is there a method of telling Excel to start looking in z:\ABC instead of the current path? This would eliminate navigating through the entire path for each link.
A macro that handles the hyperlink and puts up a navigation window or something like that would be ok.
How to save a file, with file- name. but the directory is to be read in worksheet "towns" in Cell1 (brussels) and filename in worksheet "names" in cell B2 (i.e. winter), so it saves to c:russelswinter.txt as a wordpad or kladblok txt file, that keeps a number, so each time we push a button "go back from worksheet names to worksheet towns" the "number" that is saved in the txt document goes up by value +1. In Flemisch, the "old" code goes as follows, and saves the number in the txt file Factuurnummer7.txt. But I want that the file name (here: FactuurNummer7) can be a variable text issue, which has to be read - as already noticed - in cell B2 (with the word WINTER). So the are 2 worksheets: towns, ans names
pad$ = Application.DefaultFilePath 'controle = Dir(pad$ + "FactuurNummer7.txt") 'If controle = "" Then GoTo EerstAanmaken 'Open pad$ + "Factuurnummer7.txt" For Input As #10 'Input #10, Nummer1 'Close #10......................
Below is the current code I have for File Copy before the workbook closes. This file will be distributed all over and obviously will not have the same old path and new path locations as I have in my code also will not have the same file name. Is there anyway to still perform the file copy without knowing the old path and file name and possibly have message box pop up to ask the copy to location and use that in the new path string?
Sub Macro1() Dim fs As Object Dim oldPath As String, newPath As String oldPath = "I:EXLDATAMC Daily" '<---Where the file is currently located newPath = "H:South RegionOrlando Mail Services2008DI" 'Since the super shared drive is super slow we will just copy and replace this file each time before we close and of course after we save Set fs = CreateObject("Scripting.FileSystemObject") fs.CopyFile oldPath & "" & "OrlandoMail.xls", newPath & "" & "OrlandoMail.xls" Set fs = Nothing End Sub
I just found a code to copy file path and file contents. However it is copying folder path and folder contents.here is my requirement.I will specify a path, macro has to copy that file name in a particular cell, then it has to copy all its contents. ex:
file name 1 has 3 sub files in it, file name 2 has 2 sub files in it--
I had copied the file with a new name and changed the target column to 14 (Column N) and it did not work. I went back to the original file with column 10 and it does not work either.
Each month I am sent a spreadsheet which contains a list of 100's of different file paths (including file name).
For example (Quater 1 is the document name) R:ManagementJohn2008SalesSectionAQuater1
I would like to remove the file name from the file path (I will then convert the file paths to hyperlinks which will open the folder the document is stored in but not the document itself.
I am trying to separate a filename from a filepath in Excel VBA 2003.
I am using the Application. GetOpenFilename command to get the user to select the correct file. However, this also seems to record the filepath.
I am trying to separate the filename out of this so that each file( name) activates a different sub procedure. I am storing the result of the getopenfilename as a Variant.
At the moment I am having to specify the full path in order to get each sub to run. This is fine for 1 user - but not for others as the files are stored in different locations for each user.
I'm using this code to import a file, but I want to be able to save just the path (with no file name) to a variable to use for the output file. This code saves the path and file.
Any help would be greatly appreciated. I didn't know but what there was a function that I could apply to the 'FileName' variable that would remove the file name...don't know.
How can I get the name of a workbook WITHOUT the path when I have the name WITH the path?
To be more specific. When I use "Application. GetOpenFilename" the result is a file name for say "my_book" including the path. With this I can open the file using "Workbooks.Open" and Excel will show me "my_book". But when I want to reference this workbook later on in say "Workbooks(my_book)", then I need the file name without the path. How can I get that one in VBA?
Till now I was using "CurDir" to find out the path after opening the file and just subtracted it from the full file name. This seamed to be fine. But now I had a situation where CurDir gave me a path with abbreviations (~),
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
I have a WinForm, which I'm calling via VBA's Shell command:
Code: Sub Button1_Click() Shell "C:TemplateFuncitons.exe " + ActiveWorkbook.FullName, 1 End Sub
I made this module an Excel Add-in and added it as a button in the quick access toolbar. Now, the idea is for the WinForm.exe and the Excel.xla to always be in the same directory (go hand in hand). I want to make a relative path for the .exe which will be the path to the folder where the .xla is + the string "TemplateFunctions.exe" in the end.
I am having trouble with a macro of mine. Essentially it opens a file, based on user input (Full path is stored in variable FilePath), but then I want to select that workbook, and therefore need just the .xls filename. I figured I would create a new variable, FileName, to pull out the filename.
How can I edit the string in my macro to extract just the filename.xls from the full path?
I have a UserForm with a button that, when clicked, opens up a File Directory Browser that the user can select the file of their choice.
The code is below :
Private Sub DirectoryBrowser_Click() Dim fn fn = Application.GetOpenFilename 'can add parameters. See help for details. If fn = False Then MsgBox "Nothing Chosen" Else MsgBox "You chose " & fn 'now that you have the name, you can open it or do something else End If Workbooks.Open fn End Sub
What I would like is the filepath (of the file they choose) to appear as the displayed caption on another button or label.
We have built quite a large Excel based program that contains quite a lot of coding. the program seems to work fine the majority of the time but every so often we recieve an error message that causes excel to not save, or to crash and shut down.
The message that comes up is "File/Path Error"
Now I've just done some hunting around online and some links I have found speak of declairing the document location correctly in the coding. I was wondering could this be the issue at hand?
Anytime the document needs to save, there is a cell with the directory path and file path declared as a variable (Actually as Dim String) within the coding. Is this the best way to declare a file path?
Also, the problem seems intermittent. We Compile the VBA coding before saving, we also use a Clean Project add-in within Excel. These processes usually help but every so often the problem comes back.
How can I run a DOS .bat (batch) file from within a Macro? This is what I came up with so far and it seems to work. The only thing is that I need this to have relative paths, running from the same directory the Excel Workbook is in.
