Okay - it *DID'NT* work! The reason it worked the first time was that the Text to Display was seen as a local path, but the actual File to Link TO was still the network path.
On the CD, I have New Folder. Inside is my Excel file with hyperlinks to Word docs and the folder ("Narratives") containing those docs. The hyperlinks all show AND link to \\Narratives\'docname'.doc ('docname' is actually the document name).\
BUT -- "Cannot open specified file."
Any help is appreciated.
Ed
Thank you! I set the hyperlinks as \\foldernam\docname.doc, and placed the folder with the linked docs inside the parent folder containing my spreadsheet. After I burned the CD, I gave it to a co-worker and it worked great!
Your assistance was greatly appreciated.
Ed
I post a spreadsheet on our server which contains hyperlinks to documents on the server. I have been asked to copy this file and all associated linked files to a CD. Since the CD drive letter can change with machines, how do I write the hyperlinks so they point back to the files? The linked files and the spreadsheet can all be contained within a single parent folder.
Any help or pointers to help are appreciated.
(Note: this was also posted to the Excel.misc newsgroup, but I don't know if my post went through - the network connections are kind of flakey right now.)
Ed
My employer has a program to reimburse for education. I've got a verbal approval for OzGrid's training - IF I can make it apply to my job.
I'm actually a Technical Writer (of sorts) - taking other people's words and making them look pretty. What I'm doing with VBA is actually not part of my job description, although it has helped all of us writers and I have gotten some excellent kudos for it. I just need to write up a more formal request and run it through the chain.
Oh, and get a computer at home so I can do the training! Unfortunately, my little 68030 Macintosh LCII (running at a blaxzing 33 MHz, with 8 MB RAM and a 20MB HD I would *never* fill up!) doesn't quite handle this stuff.
But my birthday is coming ...
Ed
Yes!! That did it! Thank you so much. :spin:
Now I just have to dig a bit deper into this VBA thing so I, too, can know what objects and functions go together. Maybe it's time for something a bit more in-depth than "VBA for Dummies"? :o
Ed
Okay - I got where I need to go. I can open another workbook (it's even the right one!), copy a row to the clipboard, and return to the correct cell in the correct workbook.
If I step out of the debugger here (I'm stepping through this), I can Ctrl+V and my copied data is pasted. Right-clicking, though, does not offer me PasteSpecial. I'm also losing formatting in the paste (number entered as text are pasting as numbers).
I've tried several things, and now have the following:
wb1.Activate
ActiveCell.EntireRow.PasteSpecial xlPasteValues
but get the Range object does not support this class.
I've also tried ActiveCell.EntireRow.Paste and keep getting Object does not support this.
I do this in another macro and tried copying that code directly over to this macro - but it won't work! I'm in xl2k with Win2k.
Any suggestions?
Ed
Solved that one by using
Range("C1").Select
Cells.Find(What:="D000368", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Offset(1, 0).EntireRow.Insert (xlShiftDown)
ActiveCell.Offset(1, -2).Select
That find my value, drops down, inserts a new row, then drops down and over to A of the new row.
Now I can't paste ... but that's a different topic!
Ed
Will:
Thanks for taking the time to help me. I see your point with selecting the whole column. I thought, though, by
ActiveCell.Offset(1, 0).Activate
I would be in only one cell before
Selection.EntireRow.Insert
I tried your code, and it errored on the .Find(What etc. line with "Unable to get the Find property og the range class".
Maybe I should set the whole scene. I have a workbook open and active, with Personal.xls in the background. And I start here (includes your code):
Sub InsertTIRs()
'
' InsertTIRs Macro
' Macro recorded 8/12/2003 by Authorized User
'
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim rngR As String
Dim Fname2 As String
Fname2 = "C:\Nwaccess\Data\Missing TIRs.xls"
Set wb1 = ActiveWorkbook
' Find D000368 and insert row
wb1.Activate
With Columns("C:C")
.Find(What:="D000368", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(1, 0).EntireRow.Insert (xlShiftDown)
End With
This code should find a certain cell, drop down one row, and insert a new
blank row:
wb1.Activate
Columns("C:C").Select
Selection.Find(What:="D000368", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(1, 0).Activate
Selection.EntireRow.Insert
The debugger didn't want to insert the row, telling me it couldn't shift
data off the page. I have plenty of room below my last row, so I'm assuming
that when I took it from the recorder to here and put in my parameters, I
coded something wrong. (Just a wild guess, you understand ... 8>} ).
Any help is appreciated.
BTW, Ctrl+End takes me to row 6700, the end of my data.
Ed
I came up with something that works. Don't know how pretty it is, but it gets the job done.
In Col H, I have "hi" and "bye", with H1 as "Header". In Col B, I have "go" and "nogo". Using the following code, All my "nogo" values are gone from the bottom up.
Dim rngR As String
Range("H1").Select
Selection.End(xlDown).Select
Do While ActiveCell.Value <> "Header"
If ActiveCell.Value = "hi" Then
ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate
Else: rngR = ActiveCell.Row
Range("B" & rngR).Select
Selection.Delete Shift:=xlUp
Range("H" & rngR).Select
ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate
End If
Loop
Ed
A programmer from outside wandered by my desk, and I managed to snag him to help. We came up with this:
[vba]
Sub wordopen()
Dim Word As New Word.Application
Dim WordDoc As New Word.Document
Dim Doc As String
Dim wb1 As Workbook
Dim Fname2 As String
Doc = "C:\Documents and Settings\username\Desktop\File Paths.doc"
Set WordDoc = Word.Documents.Open(Doc)
Word.Selection.WholeStory
Word.Selection.Copy
Fname2 = Application.GetOpenFilename(",*.xls")
Set wb2 = Workbooks.Open(Fname2)
wb2.Sheets("Sheet1").Select
Range("AB2").Select
ActiveSheet.Paste
WordDoc.Close
Word.Quit
End Sub
[/vba]
It stumbled when running line by line with the debugger, but ran fine all at once.
Thanks for responding. I hope this helps someone.
Ed
Does this sound right?
specify my column range
do Find for ERROR; xlPrevious
If FIND = True, get row number and delete cell in Col ?? with same row number
loop until Find = False
I have recently upgraded my typing from Search and Destroy to Hunt and Peck. Unfortunately, my VBA has yet to improve!
Thanks for the help.
Ed
Most code seems to execute from the top of a column down. I have a list on one column; if the value in the column is not valid, a formula gives me "ERROR" in a second column. I want to search for "ERROR" and delete the offending cell in the first column, shifting cells up. But that messes up the other error values if I go from the top down.
All help is greatly appreciated.
Ed
The Word doc contains a list of file paths; this doc is created by another Word macro, whcih parses a large doc into several smaller ones, and pastes the complete file path of new doc into this doc. These file paths are the only thing in this doc; no graphics, drawing objects, etc. Each path is its own paragraph.
I can manually open this document, Ctrl+A, Ctrl+C, close, then select a single cell in Excel and Ctrl+V, and the entire list of paragraphs pastes with each paragraph in its own cell. I just want to automate this.
Ed
I would like to try to write an Excel macro that opens a Word document, copies all the text, exits Word, and pastes the text into Excel at a specific location. This macro would run from within another macro.
Am I asking for the moon?
Ed
