PDA

View Full Version : Need help in creating complex macro.



Nemesis696
04-06-2011, 04:59 AM
Hi guys, need serious help here. :help
I'm using Excel 2010 and I'm new to macros. :dunno If you like some challenge, please help.
Need to create a master file (call it "FDA Master file") with multiple sheets (call first sheet "First Page"), with a button on "First Page" called "Save As and close",
which Saves as like this C:\DA\"value of 'First Page'!A14:S14" (those cells are marget) + "own text" + "value of 'First Page'!AA12:AC12" + "current date and time" + ".xlsx"
After activating "Save As and close" show notification "file name - saved in C:\DA\"file name" ", auto-close "FDA master file" and remove "Save As and close" macro button from newly created .xlsx file.

Any help, is appreciated. :friends:

Bob Phillips
04-06-2011, 05:04 AM
I would create a template file with all of the sheets and buttons that you need, and just open and save that

Nemesis696
04-06-2011, 08:05 AM
xld, thanks for quick replay!

Sorry, as I mentioned, I'm new to this macro stuff. I have converted my "FDA Master file" to Microsoft Excel Macro-Enabled Template (.xltm), and I can't really find any difference from my "FDA Master file.XLSM" and diferent way of adding macros.
So, is it even possible, to create macro to work as I specified above?


I would create a template file with all of the sheets and buttons that you need, and just open and save that

Bob Phillips
04-06-2011, 08:07 AM
You don't need any macros in that. All you need is a simple macro in say Personal.xls to open the template, and then save it in the desired name format. You could add a button or a menu item to fire the macro.

Nemesis696
04-06-2011, 08:27 AM
The thing is, that I have already found the "part" of macro to assign to my project with "save button", and it almost does the thing. But I can't add the value of marged cells to name of the file that needs to be saved, and remove the button from newly created file... Need to add cell value before and after "FDA" text.

Sub RoundedRectangle1_Click()

Application.DisplayAlerts = False

template_file = ActiveWorkbook.FullName

fileSaveName = Application.GetSaveAsFilename( _
InitialFileName:="C:\DA\FDA " + VBA.Strings.Format(Now, "-yy mmmm DD.YY HH.mm") + ".xlsm", _
fileFilter:=" Excel Macro Enabled Workbook (*.xlsm), *.xlsm")

If fileSaveName = False Then
Exit Sub
End If

ActiveWorkbook.SaveAs Filename:= _
fileSaveName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
CreateBackup:=False


file_name_saved = ActiveWorkbook.FullName
MsgBox "FDA has been successfully created at: " & vbCr & vbCr & file_name_saved

End Sub

Bob Phillips
04-06-2011, 09:26 AM
If you need to remove the button, don't put it there in the first place, have it as button on your ribbon with the code in Personal.xls, and reference Activeworkbook. Don't understand the reference to merged cells.

Nemesis696
04-06-2011, 03:06 PM
xld thanks for the suggestion,
unfortunately I have number of reasons to not use templates and add the ribbon macros. One of them as follows, I work at the mid size office with a lot of older people, who barely know, what excel is. :rotlaugh: Personally I'm using excel for making of invoices and other "fun" stuff. I'm not the system administrator and don't have access to all the computers in our office to add the templates and macros on, and I definitely don't want to explain to every senior in here how to use templates and macros. In the end, they just won't use it and will stick to old school "handwrite" (figuratively speaking) or, some of them, will endlessly bug my with the questions like "how to do that?" and "how to do this?". I have enough headache already. :banghead: So my task is, to enhance the excel file that I'm using, to make it "idiotproof" :devil2: and easy to handle, as much as possible. Then, just e-mail it to all our employees. And that's it.:whip
I was searching the internet for almost a week now, to find a macro that suits my needs, and I found the key part for my milestone. If You could suggest, the command to add merged cells value to the name of the saving file, that will work with my macro, I would be satisfied. Any suggestions are appreciated.:bow:

P.S. Sorry, I misspelled word "merged" in my previous posts.

BTW

merged cell: A single cell that is created by combining two or more selected cells. The cell reference for a merged cell is the upper-left cell in the original selected range).

Need something like this:
C:\DA\file name ("value of 'First Page'!A14:S14" & "own text" & "value of 'First Page'!AA12:AC12".xlsx)

Bob Phillips
04-07-2011, 03:27 AM
Personally, I avoid merged cells like the plague, and you know why? Because they are a pain in the butt.................

I can show you how to access merged cells, assuming I understand the problem, but I may not as I cannot see your code referencing cells at all. Where are you looking at a cell.

This is what I would do. Create a simple addin that adds a button onto the ribbon, and opens and saves the template file. Then package that template file and addin into a simple self-extracting installer, and give your bods the installer and tell them to close Excel and run that. Next time they opne Excel, voila, the addin and template are on their machine, and Excel has a button that does the work.

Nemesis696
04-07-2011, 04:17 AM
I have uploaded the file to my Drop-Box. You can find it here if you care. //dl.dropbox.com/u/16447292/FDA%20Master%20File.xlsm Just add the http: before //. I can't post links here yet.
Addin must be a good solution...
Now I have the real n00b question, how do I create the simple addin as you mentioned above? :think:

Bob Phillips
04-07-2011, 04:25 AM
This is all you need



With ActiveSheet

filesavename = "C:\DA\" & .Range("A14").Value2 & " FDA MAster File " & .Range("AA12").Value2 & ".xlsx"
End With


You build the template without the button, and once I know what you want the code to do, I will happily create it for you. Have you got a n ice discrete, strong icon we can use for the ribbon button?

Nemesis696
04-07-2011, 07:30 AM
Hi again!
Thanks for your patience! :)

Just created the template from the file I gave you this morning (only without "SAVE" button) and moved it to my excel templates folder, and added custom ribbon button to my excel (does nothing, so far - no macro attached to it).

I suppose now we can create the macro code and attach it to my newly created ribbon button?
What I need this button to do, is:

1. Save file in ".xlsx" format on our server (drive S:\\) in folder named "FDA".

Name of the file should include:
value of cell A14,
text "FDA",
value of cell AA12,
current date and time.

2. If possible, make notification of successful / unsuccessful save, and confirmation of file name and saved directory.

3. After that, auto-close excel.

Template link (http://dl.dropbox.com/u/16447292/FDA%20Master%20File%20test.xltm).
Ribbon button link (http://dl.dropbox.com/u/16447292/Excel%20Customizations.exportedUI).

Bob Phillips
04-07-2011, 09:00 AM
You have to be careful where you put the macro though if you want to distribute it, which is why I suggested an addin.

Also, when formatting the date, avoid / in the date format, you can't have that in file names.

GTO
04-07-2011, 09:20 AM
Personally, I avoid merged cells like the plague, and you know why? Because they are a pain in the butt.................

LMAO:rotlaugh: By golly, now that is clarity! I don't believe I've ever read a clearer description/explanation:clap:

Bob Phillips
04-07-2011, 02:49 PM
I was going to use the more descriptive word, but didn't want to be profane :)