Copy NonBlank rows to Word and then SaveAs

[julhs]

Hi,

My code as it is copies all Rows in the range to the opened Word Doc but I only want to copy nonblank ones.

Then I want to use SaveAs FileDialog to open at the folder location that the opened Word Doc is in. This is so I can see the other files in that folder and know what to rename this new Word Doc as, I will then manually rename it.

Help would be greatly received

julhs

Please Login or Register  to view this content.

[venkatpvc]

Please Login or Register  to view this content.

change above line to this below line and try

Please Login or Register  to view this content.

[Tinbendr]

Save the template as a word template. It should have the dotx extension. Save it to the path below.

Change to

Please Login or Register  to view this content.

Then

Please Login or Register  to view this content.

The File dialog window will show allowing you to save the file as required.

As a nit pick, you should reverse the clearing of the Word objects. Range first, then document, then application.

[julhs]

Like to start by thanking you both for your help.

Venkatpvc; afraid that has given a 1004 run time error “Command cannot be used on multiple selections” on the changed line.
One thing I forgot; that does complicate issues is, one of columns have formula in, so while it may be empty it is not blank.
If can’t do things with Copy&Paste NonBlank which I would prefer, I have been playing with this.

Please Login or Register  to view this content.

Tinbendr; be as nit-picky as you feel necessary, good way to learn.
I have changed “Open” to “Add” and “docx” to “dotm” and memory clean up. I had been testing things using a .dotm version
of template but due to the problems below and thinking that may have been the cause of it I had switched back to .docx.

Few things: filedialog does open but only as far as Documents folder (Libraries\Documents) is there a way for it
to open IN the sub folder that the template resides, so C:\Users\Julian\Documents\My Documents\Jobsheet\

Also in the File Dialog filename display box, the template doc name is not showing the same name it was opened with; instead
it’s showing what is the header of the document. Ultimately I am trying to replicate how the File dialog works when you do a
manual SaveAs normally, that way there is no navigation and very limited re-typing of a new filename.

Also; as soon as you start to navigate to the jobsheet folder, both the Excel icon in task bar and the ribbon starts
flashing until I have completed renaming and closing the document, also get message pop up saying “ Microsoft Excel is
waiting for another application to complete an OLE action”. I vaguely remember reading a post that it was due to not
declaring a variable as an Object,will look in to it.

Any further suggestions gratefully received.

julhs

[julhs]

I have figured out the FileDialoge and SaveAs part; in sorting one, the other was sorted as well.

By using:

Please Login or Register  to view this content.

and NOT changing:

Please Login or Register  to view this content.

and use the .docx version of the Template, not the .dotm version, it worked exactly as I wanted.
No navigating to a sub sub sub folder, or having to completely re-type a 30 letter file name when in fact I only had to add 4 digits to the existing file name.

Still looking into the Copy & Paste NonBlanks part, reluctant to walk away from it just yet.
At least can fall back on what I posted in message #4

I WILL succeed!

[Tinbendr]

Since you have so few cells (3x42), you probably can just write those that are not blank to Word in a string separated by commas. Then use the Convert to Table function in word to convert to a table.

Here is another sub I found that shows how to predirect and prefill the SaveAs.

Please Login or Register  to view this content.

If you provide a sample workbook and Word template doc, I can demonstrate.

[julhs]

Thanks for that, will experiment little later. Have been messing around with various options my self.

Have to go off for couple of hours, will have closer look on return.

[julhs]

Tinbendr

I have managed to put together something that gives me the desired result. It may well be very crude and undoubtedly badly
written but runs without any problems (so far). It certainly needs an experienced eye run over it to identify any flaws.

Certainly going to look into your suggestions

Any comments greatly appreciated.

[Tinbendr]

Looks good to me. But, hey, if it does what you want and you can understand it, press on!

[julhs]

That is encouraging, thank you
The last part of your comment somewhat confirms my thinking regarding vba; which
is “there is always more than one way to skin a cat” (do hope the metaphor is not lost crossing the pond)

Just one last very quick question, I promise.

Just a bit of tidying up really. When you complete the SaveAs and close the Word document you are back with the WorkBook Job Sheet in view. Annoyingly the range that was selected for copying remains highlighted, what do I need to add to the routine and where to reset the focus on the active cell on the sheet

[Tinbendr]

...to skin a cat” (do hope the metaphor is not lost crossing the pond)

Ha! No. I use it frequently.

I think this is what your asking for.

Please Login or Register  to view this content.

[julhs]

Interesting; I was in the process of trying that, however just can’t seem to get a result.
Tested placement on every line without joy, what is it I missing

[julhs]

Ok sorted it.

Application.CutCopyMode = False, wasn’t working because I had used this

Please Login or Register  to view this content.

By simply modifying it to this one line it worked

Please Login or Register  to view this content.

Tinbendr, I can’t thank you enough for your help on this, very much appreciated.


This is the final draft,

Please Login or Register  to view this content.