CODEWITHSUNDEEP
excelvbaadd-in
Ihidan
Ihidan

Reputation: 568

Store a workbook in a Xlam

There is a workbook which I need to add to a Xlam file (add-in). The idea is simple, a macro is run and then the new workbook is shown/open. One may suggest the following code:

Workbooks.Open "\\someNetworkDrive\myworkbook.xlsx"

Different users store the workbook in different paths. So the above solution doesn't work. (Moreover, The user may not have the workbook) Does any one know a way to attach the xlsx file to the xlam file?

Another approach would be to store this workbook in a VBA variable of the type workbook and then show/open it when the macro is run. But I don't know how to store an already created workbook to a VBA code.

Any idea?

Upvotes: 0

Views: 1444

Answers (2)

Charles Williams
Charles Williams

Reputation: 23550

If you need to have a single workbook solution then you need to embed the worksheets in the xlam, then create a new workbook and copy the worksheets etc into it. You may be able just to store the formats and formulas etc in your XLAM and then your VBA code in the XLAM uses them to create the new workbook. This approach works well when you have a variable number of rows etc to create.

Upvotes: 1

SierraOscar
SierraOscar

Reputation: 17647

Save over-complicating the code and just ask the user to locate the workbook instead:

Sub SO()

Dim fileLocation   As String
Dim myWB           As Excel.Workbook

fileLocation = Application.GetOpenFileName("Excel Workbook (*.xlsx), *.xlsx")

If Not UCase(fileLocaation) = "FALSE" And Not fileLocation = vbNullString Then
    Set myWB = Workbooks.Open(fileLocation)
End If

End Sub

Upvotes: 1

Related Questions