Merging all records to one single word doc 1

[digitalpencil]

Hi,
I'm trying to use access/word merge as an easy input to make up an xml doc.

Basically, so far I have an access table containing three fields 'listitem name', 'thumb' and 'stream name'. Referencing this table is a form containing the above input fields.
I wish to then take the data stored in the table and merge it with word to make an XML doc formatted as follows:

<xml>
<listitem name="<<listitem name>>" url="streams" thumb="<<thumb>>">
<stream name="<<stream name>>" start="0" len="-1"/>
</listitem>

I obviously wish to repeat this format with all records in the one word doc rather than having the above spread across 'x' docs

I would also like to close the document with an </xml> after all the records have been merged into one doc with the above format

Any advice anyone can offer on how to achieve this is greatly appreciated.

Thanks

 

[CautionMP]

digitalpencil,
Do you really need Word? If your looking for an output file in the format you specified something like this might work:

Dim rstSource As DAO.Recordset
Dim intFile As Integer
Dim strOutput As String
intFile = FreeFile
Open "C:\Output.xml" For Output As #intFile
Set rstSource = CurrentDb.OpenRecordset("SELECT [listitem name], [thumb], [stream name] FROM [i]YourTable[/i];")
Print #intFile, "<xml>"
Do
  strOutput = "<listitem name=" & Chr(34) & "<<" & rstSource.Fields("listitem name") & ">>" & _
              Chr(34) & "url=" & Chr(34) & "streams" & Chr(34) & "thumb=" & _
              Chr(34) & "<<" & rstSource.Fields("thumb") & ">>" & Chr(34) & ">"
  Print #intFile, strOutput
  strOutput = "<stream name=" & Chr(34) & "<<" & rstSource.Fields("stream name") & ">>" & _
              Chr(34) & "start=" & Chr(34) & "0" & Chr(34) & "len=" & Chr(34) & "-1" & Chr(34) & "/>"
  Print #intFile, strOutput
  strOutput = "</listitem>"
  Print #intFile, strOutput
  rstSource.MoveNext
Loop Until rstSource.EOF
Print #intFile, "</xml>"
rstSource.Close
Set rstSource = Nothing
Close #intFile

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[digitalpencil]

Thanks for your reply CMP,

Sorry, i'm not really familiar with Access..
What exactly is that i'm looking at? code for a recordset?? module?
and how exactly would I use it?

Apologies for my lack of knowledge, never really touched any database apps before.. Just figured this would be the most UI-friendly method of generating an XML doc for a web project.

Thanks again for the reply,
Digi

 

[CautionMP]

digitalpencil,
It's code for a recordset that should go in a module.

In Access:[ol]
[li]In the database window navigate to the Modules tab, then click New.[/li]
[li]This will open the VBE window and create a new Module.[/li]
[li]In the Standard toolbar select Insert -> Procedure...[/li]
[li]In the Name box type [tt]test[/tt], then click OK[/li]
[li]Now copy and paste the code from the original post between:

Public Sub test()
[i]Paste the code here[/i]
End Sub

[/li]
[li]Change the [tt]YourTable[/tt] to the real name of your table.
[tt]Set rstSource = CurrentDb.OpenRecordset("SELECT [listitem name], [thumb], [stream name] FROM YourTable;")[/tt][/li]
[li]In the Standard toolbar select Run -> Run Sub/UserForm or press {F5}[/li]
[/ol]

If you don't get any errors you should be able to navigate to the [tt]C:[/tt] drive in Windows Explorer and find a new file called [tt]Output.xml[/tt] with the data in it.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[digitalpencil]

Thanks very much for your help and step-by-step instructions.

I'm afraid I ran into a problem though.. Upon hitting 'run', I got a compile error, 'User-defined type not defined', highlighted in the code was 'rstSource As DAO.Recordset'

Thanks again for your help,

Digi

 

[CautionMP]

digitalpencil,
In the VBE pane go to Tools -> References... and check the box next to Microsoft DAO X.x Object Library, this should correct that.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[digitalpencil]

CMP, thankyou so much.. Your straight forward advice has been really beneficial and I'm now beginning to understand.

I would like to do a little editing however.
Reading your code I now understand that, & Chr(34) inserts a quotation mark, how would I insert a 'space', what is the character code for this?

Also, after having listed all the attributes, I would like to assemble the contents of "listitem name" in a long list below.
I have attempted by adding to your code so it is now the following:

Public Sub test()
Dim rstSource As DAO.Recordset
Dim intFile As Integer
Dim strOutput As String
intFile = FreeFile
Open "C:\Output.xml" For Output As #intFile
Set rstSource = CurrentDb.OpenRecordset("SELECT [listitem name], [thumb], [stream name] FROM fieldnames;")
Print #intFile, "<xml>"
Do
strOutput = "<listitem name=" & Chr(34) & rstSource.Fields("listitem name") & _
Chr(34) & "url=" & Chr(34) & "streams" & Chr(34) & "thumb=" & _
Chr(34) & rstSource.Fields("thumb") & Chr(34) & ">"
Print #intFile, strOutput
strOutput = "<stream name=" & Chr(34) & rstSource.Fields("stream name") & _
Chr(34) & "start=" & Chr(34) & "0" & Chr(34) & "len=" & Chr(34) & "-1" & Chr(34) & "/>"
Print #intFile, strOutput
strOutput = "</listitem>"
Print #intFile, strOutput
rstSource.MoveNext
Loop Until rstSource.EOF
strOutput = "<listitem name=" & Chr(34) & rstSource.Fields("listitem name") & _
Chr(34) & "/>"
Print #intFile, strOutput
rstSource.MoveNext
Loop Until rst.Source.EOF
Print #intFile, "</xml>"
rstSource.Close
Set rstSource = Nothing
Close #intFile
End Sub

I have simply added a new strOutput of 'listitem name' and syntax fomratting in another loop below rst.Source.EOF
maybe I have to reset the rst source to the beginning perhaps??
Not sure, don't really have a clue what i'm up to.. lol

Thanks again for all your help, the end-user will thank you for not having to hand-code the XML doc themselves.

 

[CautionMP]

digitalpencil,
I'm not sure what I'm doing either. I keep refering the the little instruction book that came in the box.
[ol][li]The character code for a 'space' is 32, but you don't need to use [tt]Chr()[/tt] for spaces, just add a space between quotes, i.e. " "[/li]
[li]Yes, if you want to do another loop you need to move the recordset back to the first record:

...
  rstSource.MoveNext
Loop Until rstSource.EOF
[b]rstSource.MoveFirst
Do[/b]
  strOutput = "<listitem name=" & Chr(34) & rstSource.Fields("listitem name") & _
              Chr(34) & "/>"
  Print #intFile, strOutput
  rstSource.MoveNext
Loop Until rst[red][s].[/s][/red]Source.EOF
...

[/li][/ol]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[digitalpencil]

Hi, sorry.. have successfully managed to screw it all up.
Started changing things and it wouldn't work properly and so reverted back to the original but now when I hit run, a 'macros' dialogue appears.. how do I get it to run the module again?

 

[CautionMP]

digitalpencil,
Make sure the cursor is between [tt]Public Sub test()[/tt] and [tt]End Sub[/tt] before you 'Play' the macro.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[digitalpencil]

Thanks! forgot to insert procedure, that's why I wasn't getting anywhere..
It's all working though!!
thanks again so much, you really have been a big help

Gold stars and kudos,

DigiPencil