Exporting(creating) XML files using list into a folder 1

[Triacona]

Dear all,

Please help...I'm a bit stuck

I want the following code to go through a list and create an XML file with each coresponding item in the list as the file name.
The list name is ListFileName.

Private Sub CreateAllDummyXmls_Click()
On Error GoTo Err_CreateAllDummyXmls_Click


Dim ExcelDoc As String
Dim oApp As Object
Dim sFullPath As String
Dim i As Integer


Set oApp = CreateObject("Excel.Application")
 
    For i = 0 To ListFileName.ListCount - 1
    
        sFullPath = txtPath & ListFileName.ItemData(i) & ".xml"
        MsgBox sFullPath
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, ListFileName.ItemData(i), sFullPath, True
                
    Next

Exit_CreateAllDummyXmls_Click:
    Exit Sub

Err_CreateAllDummyXmls_Click:
    MsgBox Err.Description
    Resume Exit_CreateAllDummyXmls_Click
    
End Sub

At the moment I am getting an error

Cannot update. Database or object is readonly

Please can someone tell me where I am going wrong.

The txtPath is a field within the form that contains the path of where I want these documents saved.

MS Excel workbook/spreadsheet is not open during this process.

I have tried a few variations to achieve the result (below).

Private Sub CreateAllDummyXmls_Click()
On Error GoTo Err_CreateAllDummyXmls_Click


Dim ExcelDoc As String
Dim oApp As Object
Dim sFullPath As String
Dim i As Integer


Set oApp = CreateObject("Excel.Application")

    For i = 0 To ListFileName.ListCount - 1
    
        'sFullPath = txtPath & ListFileName.ItemData(i) & ".RTF"
        sFullPath = txtPath & ListFileName.ItemData(i) & ".xml"
        MsgBox sFullPath
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, ListFileName.ItemData(i), sFullPath, False
        'DoCmd.OutputTo acOutputFunction, ListFileName.ItemData(i), acformatXML, sFullPath, False
        'DoCmd.OutputTo acOutputQuery, ListFileName.ItemData(i), acFormatRTF, sFullPath, True
        'DoCmd.OutputTo acOutputTable, ListFileName.ItemData(i), acUTF16, sFullPath, True
        'DoCmd.TransferDatabase acExport, acSchemaXSD, ListFileName.ItemData(i), sFullPath, True
        'DoCmd.OutputTo acOutputTable, ListFileName.ItemData(i), acformatXML, sFullPath, False
        'DoCmd.Save , ListFileName.ItemData(i)
        'DoCmd.OutputTo acOutput, ListFileName.ItemData(i), acformatUTF16, sFullPath, False
    Next

  
Exit_CreateAllDummyXmls_Click:
    Exit Sub

Err_CreateAllDummyXmls_Click:
    MsgBox Err.Description
    Resume Exit_CreateAllDummyXmls_Click
    
End Sub

Please help...

Thank you so much for your forthcoming help!!

Kind regards

Triacona

 

[PHV]

I'd replace this
acSpreadsheetTypeExcel3
with this:
acSpreadsheetTypeExcel12Xml

You may try to use DoCmd.RunCommand acCmdExportXML

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Triacona]

Dear PHV,

Thank you so much for your help!

I have done a little bit of debugging...
I have used the excel format (xls) to do this as this has worked for me in the past, importing excel spreadsheets based on a list into tables.

I have changed import to export...is this the wrong way to approach it...
as my code is now, just so I can test it actually creates(saves) the list as excel documents...

Private Sub CreateAllDummyXmls_Click()
On Error GoTo Err_CreateAllDummyXmls_Click


Dim ExcelDoc As String
Dim oApp As Object
Dim sFullPath As String
Dim i As Integer


Set oApp = CreateObject("Excel.Application")

    For i = 0 To ListFileName.ListCount - 1
    
        sFullPath = txtPath & ListFileName.ItemData(i) & ".xls"
        
        MsgBox sFullPath
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, ListFileName.ItemData(i), sFullPath, False
        
    Next

  
Exit_CreateAllDummyXmls_Click:
    Exit Sub

Err_CreateAllDummyXmls_Click:
    MsgBox Err.Description
    Resume Exit_CreateAllDummyXmls_Click
    
End Sub

This code gives me the following error...

The Microsoft Jet database engine could not find the object 'ListItem1'. Make sure the object exists and that you spell its name and path name correctly

Bolded, there are no objects as I want to create them within a folder(sFullPath).

So all I want to do, is create an empty dummy xml file to be saved to a location according to the list (ListFileName)

There are no records or tables to export...

Should I even be using the export command?

I want the code to go through the List(ListFileName) and from each item create a file in the specified folder(sFullPath)

The ListFileName.ItemData(i) is each corresponding item in the list.
the code

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, [b]ListFileName.ItemData(i)[/b], sFullPath, False

The bolded item is supposed the table, and there is no table as such called those items in the list.

So I'm starting to think this is not the best way to code this...
I have tried the following

DoCmd.RunCommand (acCmdExportXML)

But as I am a newby to VB, I don't really know how to use it?


Thank you for all your help.

Any more help will be greatly appreciated.

Kind regards
Triacona

 

[PHV]

So all I want to do, is create an empty dummy xml file

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
For i = 0 To ListFileName.ListCount - 1
    With fso.CreateTextFile(txtPath & ListFileName.ItemData(i) & ".xlm", True)
        .Close
    End With
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Triacona]

Dear PHV,

Thank you so so very much that is brilliant!!

It works!

Is there any really helpful reading I could find for VB regarding objects and declarations?

Thanks again!

Kind regards

Triacona