0 Replies Latest reply: May 15, 2014 9:53 AM by sxschech _ RSS

    vba ms-access to modify project file and export data example

    sxschech _

      Here is an example of using MS-Access vba to use a monarch project file, change the file names within the .xprj file and then run the project to export the data to a text file for import into access. The function reads the .xprj file and replaces all instances of the information with the new information.  In this case, the file extension within the xprj file is a date, so if the file name is ABC.20070601, and the file date of the file the user selects is ABC.20070605, then the code will go into the xprj file and replace all instances of ABC.20070601 with ABC.20070605

       

       

       

      This part of code is run from a button on an access form.  The "Call" function uses a module that is saved on the modules tab.

       

      [font="courier"] 

      Private Sub cmdAddEdit_Click()

           

          '03-May-2007: importing new data from XBA600 and XBA601 using Monarch and VBS

          Dim MonarchObj As Object

          Dim XBAFileDate As String

           

          'User chooses a file from a list and code will extract the data portion

       

          XBAFileDate = Right(fGetFileName(), 8) 'Choose any Current XBA600 or XBA601 file, extract the date extension

           

          'Fix the Monarch Project (.xprj) files to the extension date

          'Example: XBA600-07F-R2S3.20070503 will be changed to XBA600-07F-R2S3.20070603

          Call basUpdateXPRJs("0", XBAFileDate) '0=XBA600

          Call basUpdateXPRJs("1", XBAFileDate) '1=XBA601

           

          Set MonarchObj = CreateObject("Monarch32")

          MonarchObj.SetProjectFile ("C:Program FilesMonarchProjectsXBA601.xprj")

          ExportSuccess = MonarchObj.RunAllExports()

          MonarchObj.CloseAllDocuments

           

          MonarchObj.SetProjectFile ("C:Program FilesMonarchProjectsXBA600.xprj")

          ExportSuccess = MonarchObj.RunAllExports()

          MonarchObj.CloseAllDocuments

           

          MonarchObj.Exit

          Set MonarchObj = Nothing

           

          'Run the append queries to bring data into MS-Access

          DoCmd.OpenQuery "AppendXBA600"

          DoCmd.OpenQuery "AppendXBAGPA"

      End Sub[/font][/quote]Place this next code in a module

      [font="courier"] 

      Sub basUpdateXPRJs(XBAName As String, NewDate As String)

          ' Update the Project Files to reflect the XBA600 and 601 files to extract with

          ' Monarch

          ' Code modified from

          'http://www.microsoft.com/technet/scriptcenter/resources/qanda/feb05/hey0208.mspx

          'http://www.tek-tips.com/viewthread.cfm?qid=1333694

           

          Dim objFSO As FileSystemObject

          Dim strOldDate As String

           

          Const ForReading = 1

          Const ForWriting = 2

           

          strFileName = "C:Program FilesMonarchProjectsXBA60" & XBAName & ".xprj" 'Wscript.Arguments(0)

          strNewDate = NewDate 'Wscript.Arguments(2)

           

          Set objFSO = CreateObject("Scripting.FileSystemObject")

          Set objFile = objFSO.OpenTextFile(strFileName, ForReading)

           

          strtext = objFile.ReadAll

          objFile.Close

           

          '***

          'Extract the first instance of the date from the file

          'This was determined by visual observation of the position

          'of the date in the .xprj file.  This is not an actual date,

          'rather it is the file name extension given in a date format.

          '

          strOldDate = Mid(strtext, 299, 8)

          '***

           

          'Change the date in all occurences within the .xprj file

          strNewText = Replace(strtext, strOldDate, strNewDate)

           

          Set objFile = objFSO.OpenTextFile(strFileName, ForWriting)

          objFile.WriteLine strNewText

          objFile.Close

      End Sub[/font][/quote]