Avatar of CRGman
CRGman
Flag for United States of America asked on

Need VBA code to save file as XML with specific name

I am working on a database that creates an XML file for every record that is updated in a table.   The file name of the record must be named the value of one of the field names.  

This record is created to contain descriptive data about an image file that is created outside the database but the name of the image file is populated into the record that is being updated and this name is what the file name for the record being updated in the Access database needs to be named.  

Can anyone help me out with the code to:
1.   Save a file as XLM for a specific record
2.   Name a file using the value of a field name in a specific record

Thanks!
Microsoft ApplicationsMicrosoft OfficeMicrosoft Access

Avatar of undefined
Last Comment
CRGman

8/22/2022 - Mon
Patrick Matthews

It would be helpful for you to post a sample database, and some examples of the output you are expecting given that sample input.
ASKER CERTIFIED SOLUTION
trbaze

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
CRGman

ASKER
I am a novice at VBA but I am running into a problem.   I am getting a compile error on the DIM fso As Scripting.FileSystemObject

I get "User-defined type not defined"

Can you help?

I did download the Microsoft Scripting Reference file
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
trbaze

matthewspatrick is correct.  The code I provided needs the 'Microsoft Scripting RunTime' reference added.  If you use the changes he posted you do not need the reference.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
CRGman

ASKER
Ok,  that got me a little further, but now I am getting a syntax error on the:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM [tblAPInvoiceImageIndexTable] WHERE ([tblAPInvoiceImageIndexTable]![descriptFileCreated] = 0)"));", dbOpenSnapshot

If I comment out the dbOpenSnapShot the error goes away, but the code does not produce any output.

What am I doing wrong?

Code as it stands right now:

Dim fso As Object 'Scripting.FileSystemObject  Must down load Microsoft Scripting Reference in order to make this work
Dim ts As Object
Dim rs As DAO.Recordset
Dim Z As Integer
Dim strPath As String
Dim counter As Integer

Set rs = CurrentDb.OpenRecordset("SELECT * FROM [tblAPInvoiceImageIndexTable] WHERE ([tblAPInvoiceImageIndexTable]![descriptFileCreated] = 0)"));", dbOpenSnapshot
With rs
     If .BOF And .EOF Then     'no records, close recordset
          .Close
     Else     'records exist
          .MoveLast
          .MoveFirst
           
        For Z = 1 To .RecordCount   'counter rs.RecordCount 'finds number of records in recordset
               
 
            strPath = "C:\Folder\" & ![ImageNumber] & ".xml"
            Set fso = CreateObject("Scripting.FileSystemObject")
            Set ts = fso.createTextFile("strPath", True)   'added "" to strPath
           
            ts.WriteLine "<dataroot>"           'Begin data
            ts.WriteLine "<ExportData>"         'Begin Record
           
            ts.WriteLine "<VendorNumb>" & ![VendorNumb] & "</VendorNumb>"                         'Field
            ts.WriteLine "<InvoiceNumber>" & ![InvoiceNumber] & "</InvoiceNumber>"                'Field
            ts.WriteLine "<ImageNumber>" & ![ImageNumber] & "</ImageNumber>"                      'Field
            ts.WriteLine "<Invoice Date>" & ![Invoice Date] & "</Invoice Date>"                   'Field
            ts.WriteLine "<CommentHeader>" & ![CommentHeader] & "</CommentHeader>"                'Field
            ts.WriteLine "<TotalInvoiceAmt>" & ![TotalInvoiceAmt] & "</TotalInvoiceAmt>"          'Field
            ts.WriteLine "<Vendor-InvoiceNumb>" & ![Vendor-InvoiceNumb] & "</Vendor-InvoiceNumb>" 'Field
         
            ts.WriteLine "</ExportData>"        'End of Record
            ts.WriteLine "</dataroot>"          'End of Data
            ts.Close
            Set ts = Nothing
            Set fso = Nothing
           
            Next Z
            .Close
     End If
End With
rs.Close
Set rs = Nothing
                                           

End Sub
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
CRGman

ASKER
I am getting close.   The is compiling without error but it is still not writing a file.

Here is what I have now:

Dim fso As Object 'Scripting.FileSystemObject  Must down load Microsoft Scripting Reference in order to make this work
Dim ts As Object
Dim rs As DAO.Recordset
Dim Z As Integer
Dim strPath As String
Dim counter As Integer
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [tblAPInvoiceImageIndexTable] WHERE ((([tblAPInvoiceImageIndexTable]![descriptFileCreated]) = 0));", dbOpenSnapshot)

With rs
        If .BOF And .EOF Then    'no records, close recordset
            ' rs.Close
        Else
            .MoveLast
            .MoveFirst
                   
            For Z = 1 To .RecordCount   'counter rs.RecordCount 'finds number of records in recordset
               
                strPath = "C:\Data\PurchasingDatabaseUpgrade\" & ![ImageNumber] & ".xml"
                Set fso = CreateObject("Scripting.FileSystemObject")
                Set ts = fso.createTextFile("strPath", True)   'added "" to strPath
           
                ts.WriteLine "<dataroot>"           'Begin data
                ts.WriteLine "<ExportData>"         'Begin Record
           
                ts.WriteLine "<VendorNumb>" & ![VendorNumb] & "</VendorNumb>"                         'Field
                ts.WriteLine "<InvoiceNumber>" & ![InvoiceNumber] & "</InvoiceNumber>"                'Field
                ts.WriteLine "<ImageNumber>" & ![ImageNumber] & "</ImageNumber>"                      'Field
                ts.WriteLine "<Invoice Date>" & ![Invoice Date] & "</Invoice Date>"                   'Field
                ts.WriteLine "<CommentHeader>" & ![CommentHeader] & "</CommentHeader>"                'Field
                ts.WriteLine "<TotalInvoiceAmt>" & ![TotalInvoiceAmt] & "</TotalInvoiceAmt>"          'Field
                ts.WriteLine "<Vendor-InvoiceNumb>" & ![Vendor-InvoiceNumb] & "</Vendor-InvoiceNumb>" 'Field
         
                ts.WriteLine "</ExportData>"        'End of Record
                ts.WriteLine "</dataroot>"          'End of Data
                ts.Close
                Set ts = Nothing
                Set fso = Nothing
           
            Next Z
           
        End If
       
End With

rs.Close
Set rs = Nothing



What would cause this code to not generate an output?
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
CRGman

ASKER
Ok  I tried checking some variable values and I have determined that the counter values are being populated and that there is 20 records in the recordset.   I have also determined that the path string is working.  It appears the variable names being written to the XML file are not being populated.  

There must either be an error in the names of the variables.   Do I need to create DIM statements for each of these field values/variables?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
CRGman

ASKER
Ok,  thanks...yea I caught the missing .movenext statement.