Use cell value in the file name when SaveAs

[julhs]

So if cell “A17” = October 2014
and I am using:-

Please Login or Register  to view this content.

it gives me file path name:-

Please Login or Register  to view this content.

Two questions;
1) how can I lose the double spacing between “# October”
2) but what I would much prefer it to look like is,

Please Login or Register  to view this content.

I have experimented with “Trim” but just can’t get the syntax correct.

Can any body help me with this?

[TMS]

What's really in A17? Is it a real date?


Regards, TMS

[julhs]

Thanks for your response TMS.

Yes it is an actual date, just formatted mmmm yyyy

julhs

[TMS]

So, if you use .Text, you get what you see. Use Format with whatever formatting constant you require, for example, "mm yyyy". Can't test it right now but you should be able to get the brackets in ... which should answer both questions.


Regards, TMS

[julhs]

Made changes as suggested to this,

Please Login or Register  to view this content.

but getting Run-time error 438, Object doesn’t support this property or method.
At present not tried including brackets around (yyyy)

[TMS]

No, not quite.

Please Login or Register  to view this content.

Regards, TMS

[julhs]

Getting

Please Login or Register  to view this content.

at “SaveAs2” now, at a loss to know why

[TMS]

What is "SaveAs2"? I was assuming that it was a subroutine that you had created.

I can't test this is as I don't have the workbook, the code, the data, the Word document.

I can only help you fix the errors that you describe.

The code I suggested works to set up the file name, as can be seen in the second example. Note that wdPath is an undefined variable and, as a result, is empty in the output string.

Please Login or Register  to view this content.

Regards, TMS

[julhs]

It IS part of a working subroutine, that’s why I don’t understand why I am getting that error code now, after what I would class as only a subtle change to the FileName format.
“SaveAs2” is a carryover of some old testing code, simply replacing it with “SaveAs” and my code still works.

I will further digest what you have said and given me and see if I can figure things out, but I may have to come back to you if that’s ok?

Many thanks
julhs

[TMS]

I may have to come back to you if that’s ok?

Sure, but there's not much I can do without seeing the code in context.

Is SaveAs2 in another module? Is it Private or Public or not stated? Where is the code that is calling it? In another module? In a worksheet module? In a Form module? If it exists, I can only assume the calling module cannot find it for some reason.

Regards, TMS

[julhs]

Found the problem, it really was one of the most basic of errors!!
I had omitted the full stop preceding the “SaveAs2”

This is where the offending “.SaveAs2” fitted in

Please Login or Register  to view this content.

Lesson learnt here is to keep things tidier when testing code, that way I may have spotted it sooner.

TMS, thank you so much for all your help, but most of all your patience.

julhs

[TMS]

You're welcome. Thanks for the rep.


And I have learned something too ... totally unaware that there was a SaveAs2 method for a Word document.

Regards, TMS

[julhs]

Credit for the SaveAs2 belongs to “macropod”

regards, julhs