Posts by EdNerd

    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.


    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.


    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.)


    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 ...


    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:
    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?


    Solved that one by using
    Cells.Find(What:="D000368", After:=ActiveCell, LookIn:=xlValues, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    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!



    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

    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
    With Columns("C:C")
    .Find(What:="D000368", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    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:
    Selection.Find(What:="D000368", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    ActiveCell.Offset(1, 0).Activate

    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.


    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


    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


    A programmer from outside wandered by my desk, and I managed to snag him to help. We came up with this:
    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)

    Fname2 = Application.GetOpenFilename(",*.xls")
    Set wb2 = Workbooks.Open(Fname2)


    End Sub
    It stumbled when running line by line with the debugger, but ran fine all at once.

    Thanks for responding. I hope this helps someone.


    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.


    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.


    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.