Is it possible to create a patch or update an Excel Program?

[ynotravid]

I created a spreadsheet that has been widely distributed (well 20-30
people) and I came across a bug in my code. The bug was an easy fix
but I have no way to update all the distributed programs without a
complete mess.

Is there a way to do this?

Thanks ahead of time!

 

[Guest]

1. Create a new .xls file with the revision number in the filename.
2. e-mail the new file to your recipients

 

[securityman]

Gary's Student,

Would the receiver of this new file have to copy and paste his dat
from the old file into the new file? Or would the new file and ol
file merge without having to copy/paste and without losing any of th
data

 

[ynotravid]

um... yeah. I guess should have mentioned that there is a ton of data.
Just getting the data transfered properly to a new version takes a
couple hours.

 

[TheIrishThug]

if the only change is in the modules, than u could just export them and
then import the new code into the existing xls files. you could probly
write a macro that would do it for the user.

 

[ynotravid]

Irish, you are brilliant!

I'm off to try to figure out how.

Thanks for the suggestion!

 

[N10]

HI

Another way, which I use. is to automate Outlook. There are several Outlook
addins available which will automatically save attachments which meet
predefined criteri and thus over write the older version of the file.
Speery do one Im certain there are others.

Hope this helps

N10

 

[ynotravid]

Thanks for the input guys.

Couldn't figure a way to make the import/export work. So I'm trying t
do it by exporting to a .csv. file and then importing it on the othe
end. Not fun, but at least I know it can be done.

Thanks again guys

 

[Edward Ulle]

I have used the following to export a project to files and import into a
new workbook.

Note that you need to reference Microsoft Scripting Runtime and
Microsoft Visual Basic for Applications.

First, export the project from the lastest addition of your project
using the ExportProject macro. Second, open a new workbook and import
the ExportProjectModule.bas. Delete the ExportProjectModule.bas from
the "VBACode" directory so it doesn't get imported twice. Lastly run
the ImportProject macro.

I'm sure there's room for improvment on this, but I've gotten lazy.

Also I haven't found a way to delete modules from an existing workbook.
If any one know how, please let me know.

Attribute VB_Name = "ExportProjectModule"
Option Explicit

' Requires Microsoft Scripting Runtime
' Requires Microsoft Visual Basic for Applications Extensibility

Private Enum FILE_TYPE
MODULE_TYPE = 1
CLASS_TYPE = 2
FORM_TYPE = 3
End Enum

Const strPath = "C:\RPP\Test\VBACode\"

Public Sub ExportProject()

Dim fsoFileSystemObject As FileSystemObject
Dim vbComp As VBComponent

Set fsoFileSystemObject = CreateObject("Scripting.FileSystemObject")
If Not fsoFileSystemObject.FolderExists(strPath) Then
MsgBox "Folder does not exist <" + strPath + ">"
Exit Sub
End If

For Each vbComp In ThisWorkbook.VBProject.VBComponents
Select Case vbComp.Type
Case FILE_TYPE.MODULE_TYPE
vbComp.Export strPath + vbComp.Name + ".bas"
Case FILE_TYPE.CLASS_TYPE
vbComp.Export strPath + vbComp.Name + ".cls"
Case FILE_TYPE.FORM_TYPE
vbComp.Export strPath + vbComp.Name + ".frm"
End Select
Next

Set fsoFileSystemObject = Nothing
Set vbComp = Nothing

End Sub

Public Sub ImportProject()

Dim fsoFileSystemObject As FileSystemObject
Dim fFolder As Folder
Dim fFile As File
Dim vbComp As VBComponent

Set fsoFileSystemObject = CreateObject("Scripting.FileSystemObject")
If Not fsoFileSystemObject.FolderExists(strPath) Then
MsgBox "Folder does not exist <" + strPath + ">"
Exit Sub
End If
Set fFolder = fsoFileSystemObject.GetFolder(strPath)

For Each fFile In fFolder.Files
If fFile.Name = "ExportProjectModule.bas" Then
' Skip this file
ElseIf InStr(fFile.Name, ".bas") Then
ThisWorkbook.VBProject.VBComponents.Import strPath + "\" +
fFile.Name
ElseIf InStr(fFile.Name, ".cls") Then
ThisWorkbook.VBProject.VBComponents.Import strPath + "\" +
fFile.Name
ElseIf InStr(fFile.Name, ".frm") Then
ThisWorkbook.VBProject.VBComponents.Import strPath + "\" +
fFile.Name
End If
Next

Set fsoFileSystemObject = Nothing
Set fFolder = Nothing
Set fFile = Nothing
Set vbComp = Nothing

End Sub