Excel workbook created using VBA does not contain macros

Question

Hello I recently created a vba macro in excel to email spreadsheets. The spread sheets that I want to email are created using another vba program. The program takes a part number as input and then pops excel open and grabs the corresponding data from a sql server database.

My issue is that the excel that is popped up during the retrieval of the information does not contain the macro I created. If I copy the data and open a new blank excel workbook my macro is there. If I save the spreadsheet and then reopen it my macro also appears.

So Is the way that vba creates a new excel workbook somehow causing my macros not to appear? Is there any way to fix this? In the end I would like to email the spread sheet the minute the vba program has finished creating it. Any help would be greatly appreciated.

EDIT- Here is a code snippet as to how I am creating the excel workbook.

     ' Create new Workbook

    xlApp = New Excel.Application
    xlApp.Visible = True
    xlWorkBook = xlApp.Workbooks.Add
    xlWorkSheet = xlWorkBook.Worksheets(1)


    Array.Sort(snarry)
    On Error Resume Next

    ' Selecct Column Widths

    For i = 0 To inxms - 1
        xlWorkSheet.Range(xlWorkSheet.Cells(1, i + 2), xlWorkSheet.Cells(2, i + 2)).EntireColumn.ColumnWidth = 10
    Next i

    ' Display Measurement Types

    offst = 7
    xlWorkSheet.Cells(1 + offst, 1) = "S/N"
    For i = 0 To inxms - 1
        xlWorkSheet.Cells(1 + offst, i + 2) = measures(i)
    Next
    xlWorkSheet.Cells(1 + offst, inxms + 2) = "Ref#"


    frst = True
    For ims = 0 To inxms - 1
        frstmeas(ims) = True
    Next ims
    strSheetRev = ""
    strSheetRef = ""
    strskiprev = ""
    strskipref = ""
    offst = offst + 1
    For i = 0 To inx - 1
        snskiprev = False
        snskipref = False
        For ims = 0 To inxms - 1
            Call FillMeasurements(Conn, xlWorkSheet, pn, snarry(i), measures(ims), meastyp(ims), 1 + offst, ims + 2, inxms + 2, frst, frstmeas(ims), tester(ims), testdate(ims), numdg(ims), strRef, ckbNotes.Checked)
        Next
        '       If snskiprev Then
        ' strskiprev = strskiprev & "," & Trim(Str(snarry(i)))
        ' End If
        ' If snskipref Then
        ' strskipref = strskipref & "," & Trim(Str(snarry(i)))
        ' End If
        ' If ((Not snskiprev) And (Not snskipref)) Then
        xlWorkSheet.Cells(1 + offst, 1) = snarry(i)
        offst = offst + 1
        'End If
    Next


    ' Add Tolerances

    offst = offst + 1
    xlWorkSheet.Cells(1 + offst, 1) = "Max"
    xlWorkSheet.Cells(2 + offst, 1) = "Nom"
    xlWorkSheet.Cells(3 + offst, 1) = "Min"
    For ims = 0 To inxms - 1
        Call FillTolerances(Conn, xlWorkSheet, pn, measures(ims), meastyp(ims), 1 + offst, ims + 2, numdg(ims))
    Next ims
    offst = offst + 3

    ' Add Tester

    offst = offst + 1
    xlWorkSheet.Cells(1 + offst, 1) = "Tested By"
    xlWorkSheet.Cells(1 + offst + 1, 1) = "Test Date"
    For i = 0 To inxms - 1
        If (Not frstmeas(i)) Then
            xlWorkSheet.Cells(1 + offst, i + 2) = tester(i)
            xlWorkSheet.Cells(1 + offst + 1, i + 2) = Format(testdate(i), "MM/dd/yyyy")
        End If
    Next

    Conn.Close()

    ' Display Header

    midloc = CInt((inxms + 1) / 2.0)
    If (midloc < 4) Then
        midloc = 4
    End If
    xlWorkSheet.Cells(1, midloc) = "XXXXXXXXXXXX"
    xlWorkSheet.Cells(2, midloc) = txtTitle.Text
    xlWorkSheet.Cells(3, 1) = "XXXXXXXXXXXXX"
    xlWorkSheet.Cells(4, 1) = "XXXXXXXXXXXXXXXXX"
    xlWorkSheet.Cells(5, 1) = "XXXXXXXXXXXXXX"
    xlWorkSheet.Cells(3, inxms) = "XXXXXXXXXXX"
    xlWorkSheet.Cells(3, inxms + 1) = pn & "-" & strSheetRev
    xlWorkSheet.Cells(3, inxms + 2) = vendor
    'xlWorkSheet.Cells(4, inxms) = "Reference #"
    'xlWorkSheet.Cells(4, inxms + 1) = strSheetRef

    ' Display messages for skipped sns

    strSkip = ""
    If (strskiprev <> "") Then
        strSkip = strSkip & "S/Ns skipped for Rev mismatch" & Chr(10) & Mid$(strskiprev, 2) & Chr(10) & Chr(10)
    End If
    If (strskipref <> "") Then
        strSkip = strSkip & "S/Ns skipped for Ref mismatch" & Chr(10) & Mid$(strskipref, 2)
    End If
    If (strSkip <> "") Then
        MsgBox(strSkip)
    End If

    '
    'xlWorkSheet.SaveAs("C:\Users\XXX\" + pn + "-" + Format(Now(), "yyyy-MM-dd-hh-mm-ss") + ".xlsx")
    ' xlWorkBook.Close()
    'xlApp.Quit()

    txtPN.Text = ""
    '        txtSNs.Text = ""
    txtTitle.Text = ""
    ckbNotes.Checked = False
    GoTo Dne

BadSN: MsgBox("Bad SN specification") GoTo Dne Dne: End Sub

OK, not sure why it's reasonable to expect that a "new blank excel workbook" should contain macros. Are you sure you're not tricking yourself here either via use of the XLSTART folder or your own default template? And code, show some code, such as how you're creating the new workbook. — rskar
Ill add the code for how I'm creating the excel workbook. I was expecting the new excel workbook to contain the macros because I created an excel add in so I assumed no matter what sheet I opened or what new blank workbook was made my macros would show regardless. Is that not the case? — Rizwan Chaudry

rskar rskar · Accepted Answer · 2015-09-08T14:29:22

An "Add-in" is something that is installed on and located to a particular computer. It does not get embedded within a workbook. Instead there is an application-wide setting which indicates whether an add-in is "installed" or not. In VBA, property Application.AddIns(index).Installed can be used to enable or disable an add-in (identified via index, which can be the name of the add-in). Unfortunately, "Installed" is a misnomer; really it means enabled/disabled, and installation is another matter entirely. If your add-in is essential, it will need to be installed on each computer that requires it.

Excel workbook created using VBA does not contain macros

1 Answers