none
VBA Add-ins - ActiveSheet & ActiveWorkbook not working RRS feed

  • Question

  • I've turned my macros into an Add-In so they can be easily installed for use in others' workbooks.

    However, all the ActiveSheet & ActiveWorkbook lines no longer work because the macro is not stored in the same workbook since it's in the XLA file.

    What do you do get get around a problem like this?

    For example:

                

    Public Sub SaveNewControlCsv()

        Dim savePath As String
        Dim todayDate As String
        Dim tempSLS As String
        Dim C As Integer

        savePath = ActiveWorkbook.Path
        todayDate = CStr(DatePart("yyyy", Date)) + "-" + CStr(DatePart("m", Date)) + "-" + CStr(DatePart("d", Date))
        tempSLS = CStr(Range("A2"))

        ChDir savePath
        ActiveWorkbook.SaveAs fileName:= _
            savePath + "\NEWCONTROL_" + tempSLS + "_" + todayDate + ".csv" _
            , FileFormat:=6, CreateBackup:=False

                ActiveSheet.Next.Select

    This works when it's on the same workbook but doesn't work as an Add-in.

    Wednesday, February 5, 2014 10:17 PM

All replies

  • In what way does it not work? Do you get an error or is the output not where you expect it to be.

    Try debugging the code to determine what excel thinks is the activeworkbook and what the variables contain.

    Public Sub SaveNewControlCsv()
    
        Dim savePath As String
        Dim todayDate As String
        Dim tempSLS As String
        Dim C As Integer
    
        savePath = ActiveWorkbook.Path
    
    Debug.Print savePath
    
        todayDate = CStr(DatePart("yyyy", Date)) + "-" + CStr(DatePart("m", Date)) + "-" + CStr(DatePart("d", Date))
    
    Debug.print todayDate
    
        tempSLS = CStr(Range("A2"))
    
    Debug.print tempSL
    
    Debug.print         savePath + "\NEWCONTROL_" + tempSLS + "_" + todayDate + ".csv"
    
        ChDir savePath
        ActiveWorkbook.SaveAs fileName:= _
            savePath + "\NEWCONTROL_" + tempSLS + "_" + todayDate + ".csv" _
            , FileFormat:=6, CreateBackup:=False
    



    Cheers,

    Andy
    www.andypope.info

    Thursday, February 6, 2014 11:25 AM
  • Excel creates the addin and it thinks it's the workbook (seperate from the one that actually has the content). I am trying to make it a permanent add in where it just does the action in the active workbook (the one that has the content, not the one with the BAS file and modules) 

    I hope it's not too confusing..

    Wednesday, February 12, 2014 11:10 PM
  • A little, but more importantly it does not give as any more information to help resolve the problem.

    Did you try the debug code I suggested?

    Is the created addin really an addin. Is the IsAddin property set to true?


    Cheers,

    Andy
    www.andypope.info

    Thursday, February 13, 2014 10:01 AM
  • If this is Excel's addin - not COM, use Thisworkbook, not Activeworkbook for save changes in it.

    Anyway if you want good tool for CSV - i can recommend you one of my


    Oskar Shon, Office System MVP - www.VBATools.pl
    if Helpful; Answer when a problem solved

    Saturday, February 15, 2014 10:57 AM
    Answerer