Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 4 of 5 FirstFirst ... 2345 LastLast
Results 31 to 40 of 43

Thread: excel to excel, excel to word

  1. #31
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Isn't that just so cool, Nancy?

    Let me know if you want to pursue that fantasy Word macro you were talking about....or was this is?
    ~Anne Troy

  2. #32
    Board Regular
    Join Date
    Mar 2002
    Location
    Massachusetts, USA
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dreamboat: Yes, Ideally this macro would be from excel to word, but I could never get it to work. THIS macro took me forever. I've tried it many times this PM, and seems to work...

    Any advice on excel to word would be very helpful!!!!

  3. #33
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Glad to hear you're up and running on 1-4 Nancy. On to #5.

  4. #34
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'd like to get this party started with respect to #5:

    Code:
    Sub WordUp()
    Dim WdObj As Object, fname As String
    fname = Sheets(2).[a1].Value
    Set WdObj = CreateObject("Word.Application")
    WdObj.Visible = False
    Sheets(1).UsedRange.Copy
    WdObj.documents.Add
    WdObj.Selection.Paste
    Application.CutCopyMode = False
    If fname <> "" Then 'make sure fname is not blank
    With WdObj
        .ChangeFileOpenDirectory "c:temp"
        .ActiveDocument.SaveAs Filename:=fname & ".doc"
    End With
    Else:
    MsgBox ("File not saved, naming range was botched, guess again.")
    End If
    With WdObj
        .ActiveDocument.Close False
        .Quit
    End With
    Set WdObj = Nothing
    End Sub
    Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-01 16:00 ]

  5. #35
    Board Regular
    Join Date
    Mar 2002
    Location
    Massachusetts, USA
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Let the party begin...NateO - I will work on this code this AM and get back to you. I don't know what I would do without your help....yes, actually, I would have jumped out of my 2nd floor office window (right after I threw my computer out the window first!).

  6. #36
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Don't jump! My dell laptop is right behind yours! Glad to be of assistance. I just started with the usedrange to get us started, if your data is too wide there could very well be some further hacking to perform.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-02 08:13 ]

  7. #37
    Board Regular
    Join Date
    Mar 2002
    Location
    Massachusetts, USA
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK - I have been playing with this code all morning. I made some changes based on another code that I found as an example that works, and tried to customize it. I realize that when i try to run it, I need another End If statement. But, I've tried placing this statment after each line, with no luck. Any suggestions? Augh....Please remember, this is only the second week I am "teaching" myself this stuff...

    Sub CreateNewWordDoc()

    Dim WrdApp As Word.Application
    Dim Wrddoc As Word.Document
    fname = ActiveSheet.[f17].Text
    Set WrdApp = CreateObject("Word Application")
    WrdApp.Visible = False
    ActiveSheet.UsedRange.Copy
    Set Wrddoc = WrdApp.Documents.Add
    Wrddoc.Selection.Paste
    Application.CutCopyMode = False
    If fname <> "" Then
    With Wrddoc
    .ChDir "Z:Typcntnextlotlatflowslbl"
    .ActiveDocument.SaveAs Filename:=fname & ".doc"
    End With
    ActiveDocument.Close
    WrdApp.Quit

    Set Wrddoc = Nothing
    Set WrdApp = Nothing
    Range("a1").Select
    End Sub

    I also realize the \ is really .

    [ This Message was edited by: nancyo on 2002-05-02 09:33 ]

  8. #38
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Alrighty, you forgot to close your if statement. I'd use the object for your dim unless you want to manually link your vb editor (manual: ). And no need for the 2nd dim statement. Don't think chdir's gonna work. Try the following:


    Sub CreateNewWordDoc()
    Dim WdObj As Object, fname As String
    fname = ActiveSheet.[f17].value
    Set WdObj = CreateObject("Word.Application") 'need the . seperator
    WdObj.Visible = False
    ActiveSheet.UsedRange.Copy
    WdObj.documents.Add
    WdObj.Selection.Paste
    Application.CutCopyMode = False
    If fname <> "" Then 'make sure fname is not blank
    With WdObj
    .ChangeFileOpenDirectory "Z:Typcntnextlotlatflowslbl" 'Word's Annotation
    .ActiveDocument.SaveAs Filename:=fname & ".doc"
    End With
    End If 'This is the missing END IF
    With WdObj
    .ActiveDocument.Close False
    .Quit
    End With
    Set WdObj = Nothing
    End Sub


    I put notes in to help demonstrate rationale. I pulled the range("a1").select out because in this macro, you don't select any cells. Also, I would prefer to use a sheet name or index number (vs. activesheet) so that you can run this from anywhere. But, you're the boss.

    You might want

    Code:
    Else:
    MsgBox ("File not saved, naming range was botched, Fill in f17 and rerun.")
    As part of your if statement, as this file will not save is f17 is empty.

    Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-02 10:01 ]

  9. #39
    Board Regular
    Join Date
    Mar 2002
    Location
    Massachusetts, USA
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK - will try this PM. I prefer a non-specific macro. I will set up in a totally different module. I appreciate the notes, this should help me understand (hopefully) what I am trying to do. Will get back to you ASAP...THANKS!!!

  10. #40
    Board Regular
    Join Date
    Mar 2002
    Location
    Massachusetts, USA
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    NateO: It worked!!! But, I had to remove the false after .ActiveDocument.Close.

    Now, the problem is the formatting. The file looks drastically different in word (almost expected). Any ideas about how to clean it up, if possible?

    So close, yet so far...

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •