SaveAs problem

[Aeneren]

Hello,

Ok ive tried to piece together a Code to Save my Workbook to a specific location using information gathered from (2) two cells within the workbook and have had no luck yet.

Locations are F3 that is a date in the format shown in code, Second is L6 and that is a Number.

Can someone give me a hand on fixing this code and:
1. tell me exatly were it needs to be placed.l
2. and possibly explaining how to link it to a ActiveX button that is in the workbook "CommandButton3"

Please Login or Register  to view this content.

Thank You
Aeneren

[PingPing]

fileSaveName is a string, so you should make sure that you are concatenating strings and not dates and integers.
Excel will do this for you automatically, but it is good practice to get into the habit of doing it yourself. To explicitly convert a non-string value to a string, use the 'CStr()' method eg. CStr(Range("L6"))

The default property of the Range object is 'Value' so when you specify 'Range("F3")', it is the same thing as specifying 'Range("F3").Value'. Again, Excel is doing the work for you but you should get into the habit of being explicit when calling Properties of objects, ie. use 'Range("F3").Value' and 'Range("L6").Value'.

Excel stores dates as numbers, eg. '13-Jan-2012' = 40921, so when you call Range("F3").Value you get back '40921' and not '13-Jan-2012'. You must use the Format() method to convert a date to a string in a certain format (ie. you won't need to use the CStr() method to get the value into a string as 'Format()' does that for you).

You also appear to have misplaced a " (you've put it after the word 'Format' and I think you mean't to put it before 'Format'.

The end result of the above is the following (I've added in some white space to make the fileName more readable):

Please Login or Register  to view this content.

[AlvaroSiza]

Slightly different than Ping's, but generally the same. I also cleaned up a couple of things.

1. I could not figure out why the same 'save as' action was being repeated, so I deleted the second instance.
2. I took the liberty of adding hypens between the various name components. You can delete them if it is your wish to have everything together without spacing or sight breaks.

As to your last request for specific direction to make it all happen, the following would be pasted in a standard module. From your VBEditor, "Insert" >> "Module". Paste the code below.

Please Login or Register  to view this content.

With design mode on from the developer tab, double-click (or single-click to activate, right-click, view code) your command button. This will take you to the worksheet level in which the ActiveX button resides. Your code here would simply be a call to the routine above, expressed as:

Please Login or Register  to view this content.

The "Call" in the code above is already understood by excel and not necessary. I prefer to use it as a quick visual reminder (excel turns the text color blue) that a routine residing elsewhere (in this case in a module) is being used (or called for use).

-Hope this helps-

[Aeneren]

AlvaroSiza,

Installed your script and thank you for being detailed
did have to do couple modifications as you can see (highlighted in red).
soon as i click the button im getting a
Run-time error '9':
Subscript out of range
when i debug its showing Highlighted yeloow line in code

Please Login or Register  to view this content.

[john55]

hi Aeneren,
the code works very well for me, please check the Sheet name!

Please Login or Register  to view this content.

[Aeneren]

John55,

ok i cant find whats causing the error i have opened the test folder and checked its file path and it is:

C:\Users\Brian\Desktop\Excell test folder

Im able to understand the code alot better than when i started few weeks ago but i just dont see the error.

Attached is the workbook if you would be able to take a look.

Thanks
Aeneren

[AlvaroSiza]

Change this line

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

You will need to do the same with the strFileNum line:

Please Login or Register  to view this content.

[Aeneren]

AlvaroSiza,

thank you for you quick reply, it now works and saves the file to the Correct folder but it has its name little Messed Up
4-13-2012-L6-Format

Thanks
Aeneren

[Aeneren]

figured it out i had the incorrect Cell listed in code

It works as suppose to Hat's off and thank's to:

AlvaroSiza
john55
PingPing


Thanks
Aeneren

[AlvaroSiza]

That's because Range("L6") which you specified that you wanted in your original post contains the literal string "L6". Did you mean "J6", where the 1904 is? If so, revise code to replace:

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

We can only help with the information you provide...

[john55]

hi,
Aeneren, just have a look at

Please Login or Register  to view this content.

what do you have in cell L6? , L6 gives the name!
and "Format" just make some test with & without it!

[Aeneren]

We can only help with the information you provide...

you are So correct thank you again for your Help

Aeneren