Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
possible to save macro with workbook as add-in?
I finished writing a macro. It works well. When I save the macro
as an add-in and then open the add-in, it doesn't work. It complains about codes like sheet1.Cells(1,2).select. Seems the workbook was not saved along with the macro. Is it possible to save the workbook with macro as an add-in? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
possible to save macro with workbook as add-in?
Hi John,
John Smith wrote: I finished writing a macro. It works well. When I save the macro as an add-in and then open the add-in, it doesn't work. It complains about codes like sheet1.Cells(1,2).select. Seems the workbook was not saved along with the macro. This is because an Excel add-in has no visible worksheets. So trying to select one will always result in error. What are you trying to do? Select something in the active workbook, or get the value from a cell on sheet1 of the add-in itself? If you want to interact with the user's current active workbook (which will be different than the add-in), you would use ActiveSheet or ActiveWorkbook.Worksheets(1) or similar. To interact with the add-in, you would do like you have done without using the Select method: Dim sName As String sName = Sheet1.Cells(1, 2).Value '/ instead of selecting and using Selection.Value Is it possible to save the workbook with macro as an add-in? Yes, but as I said above, the worksheets will not be visible to the end user. You can still interact with them all you want (if you don't activate or select anything). -- Regards, Jake Marx www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
possible to save macro with workbook as add-in?
Thanks for the prompt reply. What I would like to do is have the
macro generate a spreadsheet and put formula on the sheet. I tried something like application.worksheets.add() but that did not work. Jake Marx wrote: Hi John, John Smith wrote: I finished writing a macro. It works well. When I save the macro as an add-in and then open the add-in, it doesn't work. It complains about codes like sheet1.Cells(1,2).select. Seems the workbook was not saved along with the macro. This is because an Excel add-in has no visible worksheets. So trying to select one will always result in error. What are you trying to do? Select something in the active workbook, or get the value from a cell on sheet1 of the add-in itself? If you want to interact with the user's current active workbook (which will be different than the add-in), you would use ActiveSheet or ActiveWorkbook.Worksheets(1) or similar. To interact with the add-in, you would do like you have done without using the Select method: Dim sName As String sName = Sheet1.Cells(1, 2).Value '/ instead of selecting and using Selection.Value Is it possible to save the workbook with macro as an add-in? Yes, but as I said above, the worksheets will not be visible to the end user. You can still interact with them all you want (if you don't activate or select anything). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
possible to save macro with workbook as add-in?
Hi John,
OK. Since your add-in is hidden, adding worksheets to it wouldn't do you any good (that is, if you want your end user to see the new worksheet). So, you probably want to create a new workbook and add formulas to the first worksheet in that workbook, right? If so, here's how you could do it: Sub demo() Dim wb As Workbook Set wb = Workbooks.Add With wb.Worksheets(1) .Cells(1, 1).Value = 2 .Cells(2, 1).Formula = "=A1*2" End With Set wb = Nothing End Sub This is just a simple example, but hopefully it gets you started in the right direction. -- Regards, Jake Marx www.longhead.com [please keep replies in the newsgroup - email address unmonitored] John Smith wrote: Thanks for the prompt reply. What I would like to do is have the macro generate a spreadsheet and put formula on the sheet. I tried something like application.worksheets.add() but that did not work. Jake Marx wrote: Hi John, John Smith wrote: I finished writing a macro. It works well. When I save the macro as an add-in and then open the add-in, it doesn't work. It complains about codes like sheet1.Cells(1,2).select. Seems the workbook was not saved along with the macro. This is because an Excel add-in has no visible worksheets. So trying to select one will always result in error. What are you trying to do? Select something in the active workbook, or get the value from a cell on sheet1 of the add-in itself? If you want to interact with the user's current active workbook (which will be different than the add-in), you would use ActiveSheet or ActiveWorkbook.Worksheets(1) or similar. To interact with the add-in, you would do like you have done without using the Select method: Dim sName As String sName = Sheet1.Cells(1, 2).Value '/ instead of selecting and using Selection.Value Is it possible to save the workbook with macro as an add-in? Yes, but as I said above, the worksheets will not be visible to the end user. You can still interact with them all you want (if you don't activate or select anything). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro save workbook | Excel Discussion (Misc queries) | |||
Macro to save workbook | Excel Discussion (Misc queries) | |||
Macro to Save just one sheet to new workbook. | Excel Worksheet Functions | |||
"Save" macro problem, still prompted to save when closing workbook (?) | Excel Programming | |||
Use a Macro to Save a Copy of a Workbook? | Excel Programming |