Option Explicit

Category: Snippets

  • Export VBA Modules for Version Control updated September 2024

    Export VBA Modules for Version Control updated September 2024

    Add a Module in your VBA Project. Name it “Exports” and paste the following code into the module in its entirety. When your project is ready for export (and subsequent version control), click on the “Macros” button in the Developer ribbon, select “ExportVBAModules” and click “Run.”
    Option Explicit

    ‘ Define a constant for the export location
    Const EXPORT_PATH As String = “F:\VBAProjects\”

    Sub ExportVBAModules()
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim objFSO As Object
    Dim objFile As Object
    Dim strPath As String
    Dim strFile As String
    Dim LineNum As Long
    Dim modCode As String
    Dim wbName As String

    ‘ Get the name of the workbook without extension
    wbName = Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, “.”) – 1)

    ‘ Create a new folder in the specified location with workbook name and timestamp
    strPath = EXPORT_PATH & wbName & “_VBAExport_” & Format(Now, “yyyymmdd_hhmmss”)

    ‘ Check if the EXPORT_PATH exists, if not, create it
    Set objFSO = CreateObject(“Scripting.FileSystemObject”)
    If Not objFSO.FolderExists(EXPORT_PATH) Then
    objFSO.CreateFolder EXPORT_PATH
    End If

    ‘ Create the export folder
    MkDir strPath

    ‘ Reference Microsoft Visual Basic for Applications Extensibility 5.3 library
    Set VBProj = ThisWorkbook.VBProject

    For Each VBComp In VBProj.VBComponents
    Select Case VBComp.Type
    Case vbext_ct_ClassModule
    strFile = strPath & “\” & VBComp.Name & “.cls”
    Case vbext_ct_MSForm
    strFile = strPath & “\” & VBComp.Name & “.frm”
    Case vbext_ct_StdModule
    strFile = strPath & “\” & VBComp.Name & “.bas”
    Case vbext_ct_Document
    ‘ This is a worksheet or workbook object.
    ‘ Don’t export these unless you want to.
    strFile = strPath & “\” & VBComp.Name & “.cls”
    Case Else
    strFile = strPath & “\” & VBComp.Name & “.txt”
    End Select

    If VBComp.CodeModule.CountOfLines > 0 Then
    modCode = VBComp.CodeModule.Lines(1, VBComp.CodeModule.CountOfLines)
    Set objFile = objFSO.CreateTextFile(strFile)
    objFile.Write modCode
    objFile.Close
    End If
    Next VBComp

    MsgBox “Modules exported to ” & strPath
    End Sub

  • Update Access VBA Saved Imports Exports: A Step-by-Step Guide

    Updating Access VBA saved imports exports

    Updating Access VBA saved imports exports is essential when dealing with external data sources. This step-by-step guide will show you how to update Access VBA saved imports exports by dynamically changing the file path within your import/export specifications.

    In this article, we’ll walk through a powerful VBA script that allows you to update Access VBA saved imports exports easily. This technique is also applicable to Microsoft Excel, making it a versatile solution for managing external data sources across Microsoft Office applications.

    Prerequisites

    Before using the script, ensure you have the necessary references set up in your VBA project:

    1. Open the Visual Basic Editor (VBE) in Access (Alt+F11 or Database Tools tab > Visual Basic).
    2. Go to Tools > References.
    3. Check “Microsoft XML, v6.0” in the References dialog box.
    4. Click OK to close the dialog box.

    Why Update Access VBA Saved Imports Exports?

    1. Flexibility: Easily update file paths when external data source locations change.
    2. Time-saving: Avoid recreating import export specifications from scratch.
    3. Automation:Incorporate the script into your VBA modules for automated updates.
    4. Compatibility: Works seamlessly with both Access and Excel.
    5. Efficiency: Edit XML specifications directly, bypassing Access UI limitations.

      The VBA Script to Update Access VBA Saved Imports Exports

      Here’s the UpdateImportSpecPathXML script to update Access VBA saved imports exports:

      Public Sub UpdateImportSpecPathXML(ByVal strSpecName As String, ByVal strNewPath As String)
          
          Dim xmlString As String
          Dim xmlDoc As Object
          Dim xmlNode As Object
          Dim newPath As String
          Dim projectPath As String
          
          ' Get the project path
          'projectPath = CurrentProject.Path
          
          ' Define the new path
          'newPath = projectPath & "\NWEDAILY.txt"
          
          ' Get the XML string representation of the import/export specification
          xmlString = CurrentProject.ImportExportSpecifications(strSpecName).XML
          
          ' Create a new XML document object
          Set xmlDoc = CreateObject("MSXML2.DOMDocument")
          
          ' Load the XML string
          xmlDoc.LoadXML xmlString
          
          ' Set the namespace for the document
          xmlDoc.SetProperty "SelectionNamespaces", "xmlns:ns='urn:www.microsoft.com/office/access/imexspec'"
          
          ' Select the ImportExportSpecification node
          Set xmlNode = xmlDoc.SelectSingleNode("//ns:ImportExportSpecification")
          
          ' Update the Path attribute
          xmlNode.Attributes.getNamedItem("Path").Text = strNewPath
          
          ' Save the updated XML back to the specification
          CurrentProject.ImportExportSpecifications(strSpecName).XML = xmlDoc.XML
          
          'MsgBox "Path attribute updated successfully."
          
      End Sub

      How the Script Works

      1. Retrieves the XML string of the specified import/export specification.
      2. Creates a new XML document object and loads the XML string.
      3. Sets the namespace for proper XML manipulation.
      4. Selects the ImportExportSpecification node.
      5. Updates the Path attribute with the new file path.
      6. Saves the updated XML back to the import/export specification.

      To update your saved imports exports, simply call the script with the specification name and new file path.

      Step-by-Step Guide to Update Access VBA Saved Imports Exports

      1. Open your Access database and press Alt+F11 to open the Visual Basic Editor.
      2. In the VBE, go to Tools > References and check “Microsoft XML, v6.0”.
      3. Create a new module and paste the UpdateImportSpecPathXML script.
      4. Call the script with the specification name and new file path to update your Access VBA saved imports exports.

      By following these steps, you can easily update Access VBA saved imports exports and streamline your data management tasks.

      Conclusion

      Updating Access VBA saved imports exports is a powerful technique for managing external data sources efficiently. By using the provided script and following the step-by-step guide, you can dynamically update file paths within your saved specifications, saving time and effort.

      Implement this solution in your Access projects and simplify your data management workflows. If you have any questions or suggestions, please leave a comment below. Happy coding!

    1. ReturnName

      Function ReturnName(ByVal num As Integer) As String
  ReturnName = Split(Cells(, num).Address, "$")(1)
End Function

    2. DeleteInitialColumnsFromWorksheet

      Note: This function depends on ReturnName

      Sub DeleteInitialColumnsFromWorksheet( _
    ws As Worksheet, _
    NumberOfColumns As Integer _
)
    With ws
        .Columns( _
            ReturnName( _
                1 _
            ) & _
            ":" & _
            ReturnName( _
                NumberOfColumns _
            ) _
        ).Delete _
            Shift:=xlToLeft
    End With
End Sub

    3. GetFilePath

      Public Function GetFilePath( _
    sFullFileNameWithPath As String _
) As String
    Dim fs As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    GetFilePath = _
        fs.GetAbsolutePathName( _
            sFullFileNameWithPath _
        )
End Function

    4. GetFileNameFromFullPath

      Public Function GetFileNameFromFullPath( _
    sFullFileNameWithPath As String _
) as String
    Dim fs As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    GetFileNameFromFullPath = _
        fs.GetFilename( _
            sFullFileNameWithPath _
        )
    Set fs = Nothing
End Function

    5. GetBaseName

      Public Function GetBaseName( _
    sFullFileNameWithExtension As String _
)
    Dim fs As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    GetBaseName = _
        fs.GetBaseName( _
            sFullFileNameWithExtension _
        )
    Set fs = Nothing
End Function
    6. TransferFontProperties

      TransferFontProperties

      Public Sub TransferFontProperties( _
    rngSource As Range, _
    rngTarget As Range _
)
    Dim sf As Font
    Set sf = rngSource.Font
    With rngTarget.Font
        .Name = sf.Name
        .Size = sf.Size
        .Strikethrough = sf.Strikethrough
        .Superscript = sf.Superscript
        .Underline = sf.Underline
        .Color = sf.Color
        .Italic = sf.Italic
        .TintAndShade = sf.TintAndShade
        .Subscript = sf.Subscript
        .Bold = sf.Bold
    End With
End Sub
    7. TrimRange

      TrimRange

      Public Function TrimRange( _
    rngSource As Range, _
    Optional TrimTop As Integer = 0, _
    Optional TrimBottom As Integer = 0, _
    Optional TrimLeft As Integer = 0, _
    Optional TrimRight As Integer = 0 _
) As Range
    Set TrimRange = _
        rngSource.Offset( _
            rowoffset:=TrimTop, _
            columnoffset:=TrimLeft _
        ).Resize( _
            rngSource.Rows.Count - TrimTop - TrimBottom, _
            rngSource.Columns.Count - TrimLeft - TrimRight _
        )
End Function
    8. VBComponentNameIsUnique

      VBComponentNameIsUnique

      Public Function VBComponentNameIsUnique( _
    wb As Workbook, _
    strInputName As String _
) As Boolean
    Dim vbc As VBComponent
    VBComponentNameIsUnique = True
    For Each vbc In wb.VBProject.VBComponents
        If vbc.Name = strInputName Then
            VBComponentNameIsUnique = False
        End If
    Next vbc
End Function