We have business application that exports data to Excel. It does this directly, you push the button in our application and Excel starts and opens with a new sheet that displays all the lists.
Those lists are formatted badly. I have a macro that reformats (Arial 8 etc.) but I need to distribute this macro to a few hundred PCs that also have other makros installed. Thus I can't simply distribute the XLSTART file.
I only want this behaviour.
1. In our application press "Export to Excel"
2. Excel starts and displays the data.
3. A button "appears" in my toolbar (just where the bold etc. is)
4. I push this macro-button and the sheet gets reformatted.
I was thinking about an AddIn. Or how can I achieve my goal?
Code:
Sub CQ()
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Columns.AutoFit
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
yes you can create an addin for that, but the addin will have to be available to and installed for all users,
you could as an option, use a vb or other external program to reformat the sheet
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
I found out, that all Add-Ins get disable when Excel opens. The menus and bars don't appear until I close Excel and reopen it. I'd love to have this option directly in Excel. It's easier for the people to simply click on a button in an Excel toolbar or choose an entry in a menu.
I don't want to save the sheet before, I want it reformatted with a simple click.
I found out, that all Add-Ins get disable when Excel opens.
Why is this some Work Policy or something ? or does your business app open Excel with out Toolbars ??
An Add-In would normally be the perfect way to do what you are describing, but as Westconn said you would then need to make it available to your users by rolling it out to all there PC's.
Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you
I can easily distribute the addin by logon-script copy to
c:\Dokumente und Einstellungen\%username%\Anwendungsdaten\Microsoft\AddIns\
and post instructions how to enable it in "Extras". This is the easy part.
So I have to find out
a) how I can press the export button and still start the add-ins and
b) how to embedd a makro in an add-in and start it using a icon/menu
I attached my "Projects" and I put a macro in "This sheet" and in "Modul1". Now I want to link to any of these macros using a menu or a button.
you can declare your application with events in your addin, it can then work with the open event of any new workbook
my addin always starts with excel, so i do not know why yours are disabled
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Reply With Quote
if the query is solved & Rate those who have helped you 
