vba excel word 2003 Results


Page 1 of 4.
Results 1...20 of 72

Sponsored Links:



I know that you guys often provide a VBA solution so I hope you can help me. I need to do this repeatedly:

1. Working in excel say ExcelDoc.xls, which has a block of cells on each tab, consisting of text in cells and perhaps a picture as well
2. Create and open a Word file, WordDoc.doc
3. In ExcelDoc.xls, select the block and copy to clipboard (ctrl-A on each tab will do it; the range could also be named but I don't think that is necessary)
4. Jump to Worddoc.doc and paste special as enhanced metafile
5. Repeat 3 and 4 until done.

I can do this manually just fine, but I need to automate it. The macro recorder won't do it. I'm hoping someone out there can give me a VBA routine that can. The routine would preferably reside in ExcelDoc.xls and run from there.

Bonus:
It would be really good if I had a way to save each pasted "image" as a stand-alone file, blockN.emf. I presently do this by pasting the block into Visio and then SaveAs EMF file in Visio. That also works great but I need to do it without Visio.




In Word 2003, the following code works like a charm:

If Dialogs(wdDialogFileNew).Show = -1 Then
'some code here
End If

This shows the FileNew dialog where you can pick a template and, after clicking OK, a new doc is created based on that template.
I tried something similar in Excel:

If Application.Dialogs(xlDialogWorkbookNew).Show = -1 Then
'some code here
End If

This doesn't work as I expected.
First, it doesn't create a new workbook based on the chosen template. It inserted an extra Sheet in the current workbook. And there was no sign of the custom commandbar that should be created and made visible. (The code to do that works fine in Excel97 for 2 years.)

Second, Excel insists that Application. should be in front of Dialogs(...). If not, it complains with:

Compile error: Sub or Function not defined

Third, if this code is called when there's NO workbook already open, this error occurs:

Runtime error 1004: Property Show of class Dialog cannot be (found / loaded ?)
(in Dutch: Eigenschap Show van klasse Dialog kan niet worden opgehaald)

What am I doing wrong?




I read with great interest the Browsing (VBA/Excel) topic because I, too, have been looking for code similar to what was suggested for Excel, but for Word 2003. Word doesn't seem to have a similar option (but then I've missed the obvious before ).

What I'm trying to do is modify the code provided in Andrew Savikas' Word Hacks for Find/Replace in Multiple Files (Hack #31) in several ways to make it more useful for myself and my editors.

My goal is to have a three-part macro. (1) Part 1 allows the editor to identify the files or the folder and keep that location information until this particular part of the macro is run again, which would automatically clear out the information, or until Word is exited, at which time the information would be removed from the macro. This identification needs to be via a dialog box, similar to the File Open box. In the hack the path is hardwired into the macro, requiring the macro to be edited each time it is used as follows:

With Application.FileSearch
.NewSearch
.LookIn = "C:My Documents"
.SearchSubFolders = False

(2) Part 2 is the F&R in Multiple Files part, drawing the location information for the files/folder on which to run macro on from Part 1. Which raises the second problem with the hack -- the F&R text are also hardwired into the macro as follows:

Set rng = doc.Range
With rng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "Dewey & Cheatem"
.Replacement.Text = "Dewey, Cheatham & Howe"

I need to create an interactive way for my editors to enter the Find text and the Replacement text so that this part of the macro can be used and reused repeatedly for different F&Rs.

(3) Part 3: Not included in the hack, but something else that I am trying to include, is the creation a record of the text entered as the Find and as the Replacement in a separate Word document along with a corresponding list of the files (not the folder where the files were located when the F&R was run) on which the F&R was run in alphabetical order by the Find entry.

Here's what I'm trying to accomplish: We usually work on book manuscripts that run 75 to 300 chapters (1 chapter to a file). The chapters rarely all arrive together, usually arrive out of sequence, and are written by different authors. Part of our job is to make usage consistent (for example, that an acronym's meaning is the same in each chapter, such as CT always means computerized tomography and not sometimes computed tomography). Part 1 of the macro would allow the editor to easily identify where the other chapters to be searched are located. Part 2 would allow her, using the CT example, to search for every instance of CT in the chapters conform each instance to computerized tomography. Part 3, by creating the separate document, would allow her to (a) make the same F&R on additional chapters as she was assigned them, ([img]/forums/images/smilies/cool.gif[/img] tell her on which chapters she already had done the particular search on, and provide a way for other editors who are working on the same project but different chapters to make the same F&R on their chapters.

The most crucial parts are 1 and 2; part 3 would be nice but if I can get the macro to work with just parts 1 and 2, I would consider that a success. Any help on any one or more of the parts is appreciated. Thanks.




I need to set-up a system in Word to produce documents with minimum of effort. I have the clean documents - without the variable information already. What I am looking for is a way to produce a document by either prompting the user for the variable information or typing the variable stuff into a table or form and then clicking a button to produce the document.
What I am asking for is a suggestion as to which way to go:
Prompts or form?
I am familiar with both in Word and use VBA in Access and Excel already.
The other thing is - there may be an add-in for purchase that would save me a lot of trial and error. This is for an attorney - sole proprietor, with one user, maybe two.

Any help would be appreciated.

deutsch




Hi all
I am trying without success to read some cells in an excel spreadsheet from in word using vba
So I have a workbook called HHSRS The workbook has two work sheets Relmat and Hazards
I have a VBA form that has 32 labels on it . I need to read cells in the workbook on the worksheet Relmat the cells are always in Column A but may be a different range each time
The data is retrieved based on the value of a textbox on the previous form that form is called Hazarddetail
I have set a reference to excel but I always get a global error on loading the form




I am familiar with the Workbooks.Add Template:="[path and template name]" command.
But what if you don't know the default path of the templates? Is there some way to determine this? I know that the only place you can change the path of the templates is in Word, so if I wanted code to open a template in Excel, and I move to another computer which has the same template but possibly in another folder, how can I access this via VBA? Is it even possible? It would be safe to assume that Word is on both computers if this helps.
Thanks for your help.




Hi forum,

With the help of this forum, I managed to write a script that reads the custom properties of a collection of Word docs and writes the results to a file which I can then import to Excel.

Now I have to do the same thing from PDF files - that is, read the custom properties (or metadata, as Adobe calls it) from a collection of PDF files and write them to a file.

How can I read the metadata of a CLOSED PDF file from VBA?
For Word, the solution was to use DSOleFile.PropertyReader (part of the DS: OLE Document Properties 1.4 object library.

TIA,




In any VBA application (Access, Excel, Word,...2003 or 2007) and even in VB Express 2008 I have no Beep sound on one of my pc's. All other sounds are OK.
Anyone an idea ?




(Edited by StanAalbers on 16-Apr-08 10:47. Forgot to explain what I wanted to do with the fields)dear people,

I would like to create a word document with fields where some other person can fill out info. Protected, so he/she can't mess with the rest. I know how to insert the fields, but can I do the following:

1. The person who fills it out, fills out his company name (e.g. XYZ Inc) in the respective field;
2. What he filled out there, should be used to save the file: (e.g. the file name should be: NCBP [company name] & [TODAY] (resulting in "NCBP XYZ Inc 160408"

Secondly, I would like to have a message box opened when that Word file is opened. But I can not get it to work. can someone get me on track here?

I have built VBA for excel (moderate level), but I can not get this Word to work.




Has outlook vba programming in 2003 and 2007 improved much over earlier versions? I've always steered clear of it as it seems to be very user unfriendly. In earlier versions we didn't have control over all the objects or dialogs that were accessible by the user, and we were limited by having one project available in any one session. Unlike Word or Excel. Does anyone have any comments ?




(Edited by wdwells on 30-Dec-06 21:17. Note the new paragraph at the end of the post.) The following code when run from the Normal template of Word, provides the appropriate results in the Immediate window:
Sub test()
Debug.Print ActiveDocument.Name
Debug.Print ActiveDocument.VBProject.VBComponents.Count
End Sub

However, the following similar code Sub test()
Debug.Print ActiveWorkbook.Name
Debug.Print ActiveWorkbook.VBProject.VBComponents.Count
End Sub

when run from the Personal project of Excel throws the following error: Run-time error '1004':
Application-defined or object-defined error
The initial attempt to run the offending code (?ActiveWorkbook.VBProject.VBComponents.Count), from the immediate window of Excel VBA throws errors; initially Run-time error '1004':
Programmatic access to Visual Basic Project is not trusted, while subsequent attempts throw:Run-time error '1004':
Application-defined or object-defined error I don't understand the inconsistency between the results obtained when running essentially the same code in the Word and Excel versions of VBA, and would appreciate any thoughts on making the code in my Personal.xls project "Trusted".

Added Note I just tried the same test on my otther machine and find that there, in the same environment (office 2003 on XL), Word throws rthe error, while Excel performs as one would expect. Am I missing references perhaps?

T.I.A.




I apologize up front; I received instructions on how to achieve something like this several years ago, but am unable to find it in any searches.

I am creating a .csv file with a Word macro. What I need help with is code to open the newly created .csv file in Excel. At the time this code is executed Excel may be either running or not.

Any help will be greatly appreciated.

T.I.A.




We've been down this road before (at least in the Excel version), but I still manage to lose my way. So please bear with me.

The code in Module1 of CleanOutDoc.dot is reproduced below. The procedure "InternalTest" runs satisfactorily. Option Explicit
'

Public Sub InternalTest()
Call CleanUp1
Call CleanUp2(ThisDocument.Name)

End Sub
'
'
Public Sub CleanUp1()
Dim TheFileToClose As String

TheFileToClose = "No Passed Arguments"
MsgBox TheFileToClose
End Sub
'
'
'
Public Static Sub CleanUp2(TheFileToClose As String)
'
TheFileToClose = "One Argument Passed"
MsgBox TheFileToClose
End Sub

A second document contains the code I have shown below here. From this code the the first Run command executes properly, but I get an error on the second.

You will observe that there are two Run commands in this code that have been commented out. The first of these functions properly, while the second will not compile--it expects an equal sign. I am becoming more confused as time progresses. Option Explicit
'Public Sub ExternalTest()
'
Dim myvarg1 As String
Dim source1 As String
Dim source2 As String
'
source1 = "CleanOutDoc.dot'!Module1.CleanUp1"
source2 = "CleanOutDoc.dot'!Module1.CleanUp2"
myvarg1 = "my word"
Application.Run source1
Application.Run source2, myvarg1
'
' Application.Run (source1)
' Application.Run (source2, myvarg1)
End Sub

A snapshot of the Project Explorer is attached.

Can someone please explain what's going on and hopefully clear up my lack of understanding.

T.I.A.




First time poster to the Word forum.
I'm trying to avoid having to find and insert the same info in multiple places in the doc. I'd like to only insert the info once, and have it appear in the other locations. It's for a template to be used repetitively, and this would avoid entry errors. I suspect a macro, but I'm only used to Excel macros, not Word (and not VBA) I've searched in the lounge and googled without success.
Anyone have suggestions?




I have some vba code that will unprotect an excel spreadsheet. Is there somthing similar for unprotecting a word doc?

Googling around, I found 65,127 referenes to saving as HTML or RTF and then saving back to doc, blah, blah. This doesn't work for me, the doc I have, has lots of formatting and charts and tables, etc. This gets messed up pretty good when converting to other formats...

I am trying to remove password protection for a fill-in-the-blank document so I can make changes. The original author is longer with the company and the form needs to be changed. Advice? I could start from scratch but just a few cosmetic tweaks and date changes and the current form will continue to be useful.




A warm hello on a cold day in New York to all you Excel gurus...

What I need to do is loop through cells in a range (say a column) and replace every instance of two consecutive line breaks with one line break, something like Chr(10)Chr(10) being replaced by Chr(10). This is to delete blank lines within multiline cells.

I know this is a simple task in Word, but I can't seem to come up with anything that works in Excel.

As always, I am grateful and thankful for any and all advice.




For the first time in my life AFAICR, I've used an "END" statement by itself, without it being "End Function" or "End Subroutine" or "End Block" or End-anything.

I encountered problems with some Word/VBA code that accumulated statistics and called Excel repeatedly to build charts.
The first run of the macro goes fine. A second run collapses with problems with undefined objects.
I know, I know I need to know more about Objects ......

I found that (VBE) choosing Run, Reset got around the problem, so I placed an "End" immediately before the "End Sub", and the problem is submerged.
The error is still there and I must deal with it, but for the next 24 hours I can get on with life.

Public Function RunQuantifierFromFolder(strFolder As String)
''' For each document in a given folder tree
'' accumulate count in an array
Call TallySections(strFolder) ' explore folder, tally, sort array
Dim xl As Object
Set xl = CreateObject("Excel.Application")
xl.Application.Visible = True
Dim wkb As excel.Workbook
Set wkb = xl.Workbooks.Add
Call ReportTallys(wkb, "Bookmarks", strArTallyBookmarks)
(snip!)
Call ReportTallys(wkb, "Tables", strArTallyTables)
wkb.Saved = True
wkb.Close
xl.Application.Quit
Set xl = Nothing
Selection.HomeKey Unit:=wdStory
On Error Resume Next
Application.Run "MrUse.Macros.EnableSaves"
On Error GoTo 0
End
End Function




Edited by HansV to replace Word doc with the screenshot it contained

Hi,

Ive written a macro that basically imports data into excel, sorts through it putting specific data for certain people onto separate worksheets, so they can see what work they have for the day.

The macro works fine and well on my pc, but keeps bringing up the attached message, even though the scripts are exactly the same.

Ive made sure that the worksheets are NOT being called the same name......As stated above, it works on mine fine but run the script on another pc and the message appears......

Any ideas??

Cheers.
Lee




I have a client who wishes to save a worksheet as a workbook. Initially, they asked that each worksheet be saved as a seperate workbook, I found this code and it works great; however; now the story has changed... they want a specific worksheet to be saved as a workbook and for the user to be able to select the location where the sheet was being saved at the point of the VBA being run. Unfortunately, I write VBA in Word and have not worked with Excel VBA at all... any ideas on how we can modify this code to do what they want? Thanx in advance... trish

Sub ExportSheets()
For Each mySht In ActiveWorkbook.Worksheets
mySht.Copy
ActiveWorkbook.SaveAs "C:Excel" & ActiveSheet.Name
ActiveWorkbook.Close
Next mySht
End Sub




I am looking for VBA code to search the Standard toolbar for a Save button and if it is found to delete it. I also need to search the File menu for the Save command and if it is found there, then delete it. Can anyone help?

I tried using this code, that I use in Excel, but it deletes both the Save button and the Save As button. Am I on the right track?

Sub RemoveItemFromToolbar(strTBName As String, _
strButtonName As String)

Dim cbarMenu As CommandBars
Dim cctlControl As CommandBarControl

' Grab the application CommandBars object.
Set cbarToolsMenu = Application.CommandBars

' DELETE: Toolbar button.
' Loop through the CommandBars.
For Each cctlControl In cbarToolsMenu(strTBName).Controls
With cctlControl
' Check to see if the toolbar is found.
If .Caption = strButtonName Then

' If found, remove the command from the menu.
.Delete

End If

End With
Next cctlControl
End Sub

Then I call it like this:

CommandBars("Standard").Reset
RemoveItemFromToolbar "Standard", "Save"


Page 1 of 4.
Results 1...20 of 72