CODEWITHSUNDEEP
excelvba
Josh Marks
Josh Marks

Reputation: 79

How to programmatically export and import code into Excel worksheet?

We will put 100s of Excel worksheets out in the field this year. The code periodically needs to be updated when bugs are found. For last year's effort, I was able to dynamically have workbooks pull updates for .bas files. This year I want to dynamically have workbooks pull updates for the code embedded in the worksheets too.

EXPORT CODE

The export code is pretty simple, but there are artifacts in the .txt files

Sub SaveSoftwareFile(path$, name$, ext$)    
   ThisWorkbook.VBProject.VBComponents(name).Export path & name & ext

Example Call: SaveSoftwareFile path, "ThisWorkbook", ".txt"

The problem is that the export has a lot of header information that I don't care about (in red). I just want the part in blue. Is there switch that allows me not to save it, or do I have to manually go into the export and remove it myself?

enter image description here

IMPORT CODE

The import code is pretty straight forward too, but it causes the error "Can't enter break mode at this time", and I'm struggling to figure out the right path forward. If I manually try and delete this code, Excel is also unhappy. So maybe my approach is altogether incorrect. Here's the code:

Sub UpgradeSoftwareFile(path$, name$, ext$)
    Dim ErrorCode%, dest As Object
    On Error GoTo errhandler
        
    Select Case ThisWorkbook.VBProject.VBComponents(name).Type
        Case 1, 3 'BAS, FRM
             <Not relevant for this discussion>
        Case 100 'Worksheets
            Set dest = ThisWorkbook.VBProject.VBComponents(name).codemodule
            dest.DeleteLines 1, dest.CountOfLines 'Erase existing | Generates breakpoint error
            dest.AddFromFile path & name & ext '| Also generates breakpoint error
    End Select

Example Call: UpgradeSoftwareFile path, "ThisWorkbook", ".txt"

Thanks in advance for your help

Upvotes: 1

Views: 2671

Answers (1)

FaneDuru
FaneDuru

Reputation: 42256

Please, try the next way of exporting and you will not have the problem any more:

Sub SaveSoftwareFile(path$, sheetCodeModuleName$, FileName$)
    Dim WsModuleCode As String, sCM As VBIDE.CodeModule, strPath As String, FileNum As Long
    Set sCM = ThisWorkbook.VBProject.VBComponents(sheetCodeModuleName).CodeModule
    WsModuleCode = sCM.Lines(1, sCM.CountOfLines)
    'Debug.Print WsModuleCode
    strPath = ThisWorkbook.path & "\" & FileName
     FileNum = FreeFile
     Open strPath For Output As #FileNum
        Print #FileNum, WsModuleCode
     Close #FileNum
End Sub

You can use the above Sub as following:

Sub testSaveSheetCodeModule()
 Dim strPath As String, strFileName As String, strCodeModuleName As String
 strPath = ThisWorkbook.path
 strFileName = "SheetCode_x.txt"
 strCodeModuleName = Worksheets("Test2").codename 'use here your sheet name
 SaveSoftwareFile strPath, strCodeModuleName, strFileName
End Sub

Now, the created text file contains only the code itself, without the attributes saved by exporting the code...

Import part:

"Can't enter break mode at this time" does not mean that it is an error in the code. There are some operations (allowed only if a reference to Microsoft Visual Basic for Applications Extensibility ... exists) in code module manipulation, which cannot simple be run step by step. VBA needs to keep references to its VBComponents and it looks, it is not possible when changes in this area and in this way are made.

The import code is simple and it must run without problems. You must simple run the code and test its output...

Upvotes: 1

Related Questions