Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Look at the VBAMaven page. I have a homegrown solution that uses the same concepts. I have a common library with a bunch of source code, an ant build and an 'import' VB script. Ant controls the build, which takes a blank excel file and pushes the needed code into it. @Mike is absolutely correct - any duplicate module definitions will automatically have a number appended to the module name. Also, class modules (as in Sheet and ThisWorkbook) classes require special treatment. You can't create those modules, you have to read the input file and write the buffer into the appropriate module. This is the VB script I currently use to do this. The section containing @ delimited text (i.e. @build file@) are placeholders - the ant build replaces these tags with meaningful content. It's not perfect, but works for me.</p> <pre><code>'' ' Imports VB Basic module and class files from the src folder ' into the excel file stored in the bin folder. ' Option Explicit Dim pFileSystem, pFolder, pPath Dim pShell Dim pApp, book Dim pFileName pFileName = "@build file@" Set pFileSystem = CreateObject("Scripting.FileSystemObject") Set pShell = CreateObject("WScript.Shell") pPath = pShell.CurrentDirectory If IsExcelFile (pFileName) Then Set pApp = WScript.CreateObject ("Excel.Application") pApp.Visible = False Set book = pApp.Workbooks.Open(pPath &amp; "\build\" &amp; pFileName) Else Set pApp = WScript.CreateObject ("Word.Application") pApp.Visible = False Set book = pApp.Documents.Open(pPath &amp; "\build\" &amp; pFileName) End If 'Include root source folder code if no args set If Wscript.Arguments.Count = 0 Then Set pFolder = pFileSystem.GetFolder(pPath &amp; "\src") ImportFiles pFolder, book ' ' Get selected modules from the Common Library, if any @common path@@common file@ Else 'Add code from subdirectories of src . . . If Wscript.Arguments(0) &lt;&gt; "" Then Set pFolder = pFileSystem.GetFolder(pPath &amp; "\src\" &amp; Wscript.Arguments(0)) ImportFiles pFolder, book End If End If Set pFolder = Nothing Set pFileSystem = Nothing Set pShell = Nothing If IsExcelFile (pFileName) Then pApp.ActiveWorkbook.Save Else pApp.ActiveDocument.Save End If pApp.Quit Set book = Nothing Set pApp = Nothing '' Loops through all the .bas or .cls files in srcFolder ' and calls InsertVBComponent to insert it into the workbook wb. ' Sub ImportFiles(ByVal srcFolder, ByVal obj) Dim fileCollection, pFile Set fileCollection = srcFolder.Files For Each pFile in fileCollection If Right(pFile, 3) = "bas _ Or Right(pFile, 3) = "cls _ Or Right(pFile, 3) = "frm Then InsertVBComponent obj, pFile End If Next Set fileCollection = Nothing End Sub '' Inserts the contents of CompFileName as a new component in ' a Workbook or Document object. ' ' If a class file begins with "Sheet", then the code is ' copied into the appropriate code module 1 painful line at a time. ' ' CompFileName must be a valid VBA component (class or module) Sub InsertVBComponent(ByVal obj, ByVal CompFileName) Dim t, mName t = Split(CompFileName, "\") mName = Split(t(UBound(t)), ".") If IsSheetCodeModule(mName(0), CompFileName) = True Then ImportCodeModule obj.VBProject.VBComponents(mName(0)).CodeModule, _ CompFileName Else If Not obj Is Nothing Then obj.VBProject.VBComponents.Import CompFileName Else WScript.Echo "Failed to import " &amp; CompFileName End If End If End Sub '' ' Imports the code in the file fName into the workbook object ' referenced by mName. ' @param target destination CodeModule object in the excel file ' @param fName file system file containing code to be imported Sub ImportCodeModule (ByVal target, ByVal fName) Dim shtModule, code, buf Dim fso Set fso = CreateObject("Scripting.FileSystemObject") Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set buf = fso.OpenTextFile(fName, ForReading, False, TristateUseDefault) buf.SkipLine code = buf.ReadAll target.InsertLines 1, code Set fso = Nothing End Sub '' ' Returns true if the code module in the file fName ' appears to be a code module for a worksheet. Function IsSheetCodeModule (ByVal mName, ByVal fName) IsSheetCodeModule = False If mName = "ThisWorkbook" Then IsSheetCodeModule = False ElseIf Left(mName, 5) = "Sheet" And _ IsNumeric(Mid (mName, 6, 1)) And _ Right(fName, 3) = "cls Then IsSheetCodeModule = True End If End Function '' ' Returns true if fName has a xls file extension Function IsExcelFile (ByVal fName) If Right(fName, 3) = "xls" Then IsExcelFile = True Else IsExcelFile = False End If End Function </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POSource control of Excel VBA code modules
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USDaveParillo
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSource control of Excel VBA code modules
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks for this. I've looked at your script, and vbaMaven and it looks like both methods create a brand new spreadsheet and copy the macros into them. Is that correct? I was hoping to find a method to load the macros into the spreadsheet when the spreadsheet is opened - but I've completely failed to get that to work. Perhaps I'm trying to do something that can't be done with Excel. I'm now starting to look at replacing my macros with an external script which accesses Excel via COM - so at least I can keep the script under version control.
      singulars
      1. PO
      1. USJon Mills
    2. COYes, the basic idea is a 'blank' spread sheet is copied and the code is copied into it. If I had known about vbaMaven before I started, I just might have gone with it... The reason I chose to go this route is so that every line of code can be under source control. If you have code in a workbook that loads other code, then your code loading module isn't under the same level of control as everything else - it's wrpped inside an excel file.
      singulars
      1. PO
      1. USDaveParillo
    3. COAlso, you have to be very careful modifying event code on Auto_Open or Workbook_Open events. Trying to edit the ThisWorkbook object from within the Auto_Open function is a fast path to crashing excel.
      singulars
      1. PO
      1. USDaveParillo
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload