excel to excel, excel to word

[nancyo]

I am looking for a simple code to do the following:

1. Copy ONE spreadsheet from an excel workbook into a new workbook.

2. Create the new workbook as part of the macro.

3. Save the new workbook as part of the macro.

4. The saved filepath will be different only in the FILENAME, which somehow must be referenced from the original excel file (possibly from a specific cell reference from a different spreadsheet).

5. Fantasy macro - do the same to word.

I have tried several variations on macros offerend in this website and others, and can't get anything to work. I am not a programmer at all, but have been able to modify other macros in the past to work.

Any help is appreciated!!!!!!!

 

[Anne Troy]

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?

 

[nancyo]

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

 

[NateO]

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

 

[NateO]

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

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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-05-01 16:00

 

[nancyo]

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

 

[NateO]

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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-05-02 08:13

 

[nancyo]

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

 

[NateO]

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:<pre>
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</pre>

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

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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-05-02 10:01

 

[nancyo]

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

 

[nancyo]

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