I need help from pro's to finish my save and exit macro...

[michellin]

Hi guys

I'm a french guys so sorry for my english....

First i hope i post it a the right place, i reed a lot post before post mine to make sure i dont see the solution before asking, then i try very hard for over a month ....
I'm not very good in macro, i learn a lot on the internet, then i took a lot of example, then i put it together to build the macro i need. Sometime i record some too. But now i can't see it work propely.
I got an excel worbook to automatise my work, i'm a buyer but i hate to redo all the time the same thing so im going on automation.
I got a workbook with all the compagny info, and a workbook with the order sheet then the fax sheet.
I already create a macro to import the compagny info to my workbook when it open then create a validation list to choose what compagny i want to order. Then all my info are auto filling up.

The thing is: when i want to save it.... I have already do a button to link the saveandexit macro. My macro i have done right now, delete my module#2 where i got all my macro to open with the worbook to import the compagny info...then save with the date on merge cell CDEF6 then the compagny info in merge cell EF10.

But now with my macro i will paste it here, i got the way to delete module2 then save as a date from the cell with the good format, but i can get the compagny info to go with in front of the date to get like sony 2011-11-28
I got only the date, then a bug appear ......

This is my macro

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:HyphenationZone>21</w:HyphenationZone> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Tableau Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Sub saveetexit()

Dim vbCom As Object
[FONT=&quot]Set vbCom = Application.VBE.ActiveVBProject.VBComponents[/FONT]
vbCom.Remove VBComponent:= _
vbCom.Item("Module2")
Dim strDate As String
With Sheets("A remplir")
strDate = Format(Now(), "yyyy-mmm-dd")
ActiveWorkbook.SaveAs "C:\FORMULAIRE JOB SOPHIE\Fait auto\" _
& " " & strDate & ".xls"
Application.Quit
ThisWorkbook.Close
End With

End Sub


sheet1 in this example is now in french A remplir.

I hope i give you all the details you guys will need to help me, i hope my english is not very bad to not understand me....

Thanks in adavance guys

Michellin

 

[AlphaFrog]

        strDate = Format(Date, [COLOR="Red"]" y[/COLOR]yyy-mmm-dd")
        ActiveWorkbook.SaveAs "C:\FORMULAIRE JOB SOPHIE\Fait auto\" _
                              & [COLOR="Red"].Range("E10").Value[/COLOR] & strDate & ".xls"

 

[buxtongt]

Welcome to MrExcel.

First of all, your english is much better than my french, so no worries about the language barrier. In your code I see the saveas part and the strdate variable, but I don't see where you are trying to put the company name in the file name. You should be able to define that as a variable (probably from a cell in the workbook) and include it in the saveas line. Hope this helps.

 

[michellin]

Oh my god...Thanks a lot AlphaFrog

That work well but if i need to get a space between e10 and the date ??

buxtongt (In your code I see the saveas part and the strdate variable, but I don't see where you are trying to put the company name in the file name.)

That is in my cell e10 i got a validation list... so when i choose like sony my other field fetch the info on that validation list. Then i use it to save it on the name too.

I got a lot of buging trouble to get the right synthaxe with space then coma ....
But you save my day!!!

It is possible to get a space between the date and the name??

Thanks a lot man

 

[AlphaFrog]

It is possible to get a space between the date and the name??

Thanks a lot man

You're welcome (Avec plaisir).

I had put the space in the strDate line...

strDate = Format(Date, [COLOR="Red"]" y[/COLOR]yyy-mmm-dd")

 

[michellin]

GOOD GOOD GOOD Thanks a lot. And merry christmas, thats my gift