Hi All,
it's a while since I've been around these parts, so long in fact I've forgotten my login!
I have a marco that takes information from an excel sheet and moves it to a new workbook, saves the workbook in a specific folder (which it creates), with a specific name (based on the data it copied).<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The information to be saved is unique per row of the original excel sheet. <o></o>
So, for example, the macro copies the information from the first row, arranges it in a particular manner in the new workbook, creates a folder in a specific directory, saves the new workbook in the new folder. <o></o>
It then moves to the next row and repeats the process until it hits an empty row.<o></o>
This all works great! <o></o>
However, once a row has been saved to it’s new workbook I want to add a hyperlink to a particular cell in the relevant row in the original excel sheet, so I can click on this cell and it’ll open the new workbook with the relevant data. <o></o>
For the life of me I cannot seem to get this to work. <o></o>
The really odd thing is I have it working in other bits of code in the same workbook but it just refuses t o work in the current code I’m working on. <o></o>
<o> </o>
Where the code works looks like this:<o></o>
<o> </o>
Where it doesn’t work looks like this:<o></o>
<o> </o>
<o> </o>
For some bizarre reason this one doesn’t work. <o></o>
When I edit the Hyperlink this is what the macro has entered:<o></o>
<o> </o>
In the first example that works the hyperlink looks like this in the cell:<o></o>
<o> </o>
<o> </o>
<o> </o>
All the other code, making directories, saving files etc work fine, it’s just this particular macro causing me headaches and I can’t for the life of me figure out what’s wrong. <o></o>
I’m hoping its something simple and I just can’t see the wood for the trees. <o></o>
<o> </o>
Please, Please help!!<o></o>
It’s driving me crazy at this stage!!<o></o>
Let me know if you need more info.<o></o>
Best wishes,
Paul.
it's a while since I've been around these parts, so long in fact I've forgotten my login!
I have a marco that takes information from an excel sheet and moves it to a new workbook, saves the workbook in a specific folder (which it creates), with a specific name (based on the data it copied).<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The information to be saved is unique per row of the original excel sheet. <o></o>
So, for example, the macro copies the information from the first row, arranges it in a particular manner in the new workbook, creates a folder in a specific directory, saves the new workbook in the new folder. <o></o>
It then moves to the next row and repeats the process until it hits an empty row.<o></o>
This all works great! <o></o>
However, once a row has been saved to it’s new workbook I want to add a hyperlink to a particular cell in the relevant row in the original excel sheet, so I can click on this cell and it’ll open the new workbook with the relevant data. <o></o>
For the life of me I cannot seem to get this to work. <o></o>
The really odd thing is I have it working in other bits of code in the same workbook but it just refuses t o work in the current code I’m working on. <o></o>
<o> </o>
Where the code works looks like this:<o></o>
Code:
Dim Co As String<o:p></o:p>
Dim Link As String<o:p></o:p>
Dim Project As String<o:p></o:p>
<o:p> </o:p>
Link = "R:\Dept\General Licensing\General Licensing Summary (DO NOT EDIT)\Sync Licensing Applications\"<o:p></o:p>
Cells(rowno1, 7).Select<o:p></o:p>
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Link & Co & " - " & Project & ".xlsm", TextToDisplay:="View Report"
Where it doesn’t work looks like this:<o></o>
<o> </o>
Code:
Dim CompanyName As String<o:p></o:p>
Dim ProjectTitle As String<o:p></o:p>
Dim TrackTitle As String<o:p></o:p>
Dim Publisher As String<o:p></o:p>
Dim Link As String<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Link = "R:\Dept\General Licensing\General Licensing Summary (DO NOT EDIT)\Sync Licensing Applications\Publisher Sync Requests\"<o:p></o:p>
<o:p> </o:p>
Cells(rowno1, 7).Select<o:p></o:p>
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _<o:p></o:p>
Link & CompanyName & " - " & ProjectTitle & "\" & TrackTitle & " - " & Publisher & ".xlsm", TextToDisplay:=Publisher<o:p></o:p>
<o:p> </o:p>
For some bizarre reason this one doesn’t work. <o></o>
When I edit the Hyperlink this is what the macro has entered:<o></o>
<o> </o>
Code:
[COLOR=black][FONT=Calibri]Publisher%20Sync%20Requests\Test%20Licence%20-%20test%20sync1\Star%20-%20PM%20Productions.xlsm<o:p></o:p>[/FONT][/COLOR]
<o:p> </o:p>
<o> </o>
Code:
\\dublin1\DATA\Dept\General Licensing\General Licensing Summary (DO NOT EDIT)\Sync Licensing Applications\Test Licence - test sync1.xlsm
<o> </o>
All the other code, making directories, saving files etc work fine, it’s just this particular macro causing me headaches and I can’t for the life of me figure out what’s wrong. <o></o>
I’m hoping its something simple and I just can’t see the wood for the trees. <o></o>
<o> </o>
Please, Please help!!<o></o>
It’s driving me crazy at this stage!!<o></o>
Let me know if you need more info.<o></o>
Best wishes,
Paul.