Solved

export access query to txt file using vba

Posted on 2010-09-01
4
452 Views
Last Modified: 2012-05-10
I have the following code which i have tried to amend to give me the following output

<data>
<variable name="Platforms">
<row>
<column>Platform</column>
<column>Count</column>
</row>
<row>
<column>21CN</column>
<column />
</row>
<row>
<column>PDH</column>
<column>3</column>
</row>
<row>
<column>POWER</column>
<column />
</row>
<row>
<column>PSTN</column>
<column />
</row>
<row>
<column>PSTN AXE10</column>
<column>1</column>
</row>
</variable>
</data>

but instead of the above i get the text below. can you help me fix this?

<Data>
<variable name="Platforms">
<Row>
<Column> 21CN </Column>
<Column>
</Row>
</Variable>
</data>
<Data>
<variable name="Platforms">
<Row>
<Column> 21CN  SUPPORT TOOLS </Column>
<Column>
</Row>
</Variable>
</data>
<Data>
<variable name="Platforms">
<Row>
<Column> 21CN CORE/METRO NODE </Column>
<Column>
</Row>
</Variable>
</data>
Public Function PlatformsXML()

   Dim sSql As String

   Dim rs As New ADODB.Recordset

   Dim sFilename As String

   Dim fhFile As Integer

   Dim sLine As String



   sFilename = "C:\Users\700605949\desktop\textfile.txt"

   fhFile = FreeFile

   Open sFilename For Output As #fhFile



   sSql = "SELECT * FROM QryPlatformIncidentVolumesCurrent ORDER BY [Platform]"

   rs.Open sSql, CurrentProject.Connection, , adLockOptimistic, adCmdText

   Do While Not (rs.EOF)

      sLine = "<Data>" & vbCrLf & "<variable name=""Platforms"">" & vbCrLf & "<Row>" & vbCrLf & "<Column>" & _

rs![Platform] & "</Column>" & vbCrLf & "<Column>" & vbCrLf & "</Row>" & vbCrLf & "</Variable>" & vbCrLf & "</data>"

      Print #fhFile, sLine

      rs.MoveNext

   Loop

   rs.Close

   Set rs = Nothing

   Close fhFile

End Function

Open in new window

0
Comment
Question by:bryanscott53
  • 2
  • 2
4 Comments
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
This should be close
Public Function PlatformsXML()

   Dim sSql As String

   Dim rs As New ADODB.Recordset

   Dim sFilename As String

   Dim fhFile As Integer

   Dim sLine As String



   sFilename = "C:\Users\700605949\desktop\textfile.txt"

   fhFile = FreeFile

   Open sFilename For Output As #fhFile



   sSql = "SELECT * FROM QryPlatformIncidentVolumesCurrent ORDER BY [Platform]"

   rs.Open sSql, CurrentProject.Connection, , adLockOptimistic, adCmdText



   Print #fhFile, "<Data>" & vbCrLf & "<variable name=""Platforms"">" & vbCrLf 



   Do While Not (rs.EOF)

      sLine = "<Row>" & vbCrLf & "<Column>" & _

rs![Platform] & "</Column>" & vbCrLf  

      Print #fhFile, sLine

      If IsNull(rs![Count] Then

          sLine =  "<Column />"& vbCrLf  

      Else

          sLine =  "<Column>" & rs![Count] & & "</Column>" & vbCrLf 

      End If 

      sLine = sLine & "</Row>" & vbCrLf

      Print #fhFile, sLine

      rs.MoveNext

   Loop

   Print #fhFile, "</Variable>" & vbCrLf & "</data>"

   rs.Close

   Set rs = Nothing

   Close fhFile

End Function

Open in new window

0
 

Author Comment

by:bryanscott53
Comment Utility
aikimark, thanks. i should have said that the query is pullng together 2 tables. I get the following error message.
"Item cannot be found in the collection corresponding to the requested name or ordinal"

I am guessing it is because the the count field comes from the tblplatforms but i cannot seem to get the code to work, any ideas?
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
Comment Utility
since I don't know what fields are returned by the QryPlatformIncidentVolumesCurrent  query, I used the most logical field name.  If you run the query, you should see the name of the column with the count value.  Change my code accordingly.
0
 

Author Closing Comment

by:bryanscott53
Comment Utility
Aikimark, many thanks for your help, apologies for the time it has taken me to get back to you.

I have amended a couple of things and it works great now
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now