Exporting a VBA module from a SharePoint file

Question

I am trying to export a VBA module from one workbook and import it into another:

Dim ProofName As Variant
Dim sDate As String
Dim CashShN As Variant
Dim wbkSource As Workbook
Dim wbkTarget As Workbook
Dim CSVModName As String

sDate = Format(ActiveWorkbook.Sheets(1).Cells(4, 2).Value, "mmddyy")
ProofName = ThisWorkbook.Name
CashShN = ActiveWorkbook.Name

Set wbkSource = Workbooks(ProofName)
Set wbkTarget = Workbooks(CashShN)
CSVModName = ThisWorkbook.Path & "\CSV Module " & sDate & ".bas"

wbkSource.VBProject.VBComponents("Module2").Export (CSVModName)
wbkTarget.VBProject.VBComponents.Import (CSVModName)

This worked when ThisWorkbook and wbkSource were on a local drive. They are now at SharePoint locations and I am getting the following error:

Run-time error 50012: Method "Export" of object '_VBComponent' failed

This occurs on this line:

wbkSource.VBProject.VBComponents("Module2").Export (CSVModName)

How do I export the VBA module from the source to the target?

Try replacing ThisWorkbook.Path with a temp folder location. See stackoverflow.com/a/424332/478884 for finding the temp folder path. FYI you can use Set wbkSource = ThisWorkbook and Set wbkSource = ActiveWorkbook - there's no need to make that round-trip using the workbook names. — Tim Williams
– Tim Williams, Commented Nov 12 at 18:41

Tim Williams · Accepted Answer · 2025-11-12 22:06:03Z

Export can't write to an HTTP location, so you could use the user's Temp folder instead:

Option Explicit

Sub Tester()
    
    Dim sDate As String, CSVModName As String
    Dim wbkSource As Workbook, wbkTarget As Workbook
    
    Set wbkSource = ThisWorkbook
    Set wbkTarget = ActiveWorkbook
    
    sDate = Format(wbkTarget.Sheets(1).cells(4, 2).Value, "mmddyy")
    CSVModName = TempPath & "CSV Module " & sDate & ".bas"
    
    wbkSource.VBProject.VBComponents("Module2").Export CSVModName 'no parentheses needed here
    wbkTarget.VBProject.VBComponents.Import CSVModName            '...or here
End Sub

Function TempPath() As String
    'https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/getspecialfolder-method
    TempPath = CreateObject("Scripting.FileSystemObject").GetSpecialFolder(2)
    'ensure ending backslash
    If Right(TempPath, 1) <> "\" Then TempPath = TempPath & "\"
End Function

Collectives™ on Stack Overflow

Exporting a VBA module from a SharePoint file

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related