CODEWITHSUNDEEP
sqlvbams-access
FilipeG
FilipeG

Reputation: 167

Access exporting file, columns to rows

I need to export a file on a specific format, currently I just export a query as an excel file:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryReport", "path" & "name.xls"

Wich leads to an excel file like this:

Name,Age,Sex
Bob,47,M
Janet,30,F
William,23,M

With qryReport being a simple select query.
Now I need to export it on a different format, it should be only one record per file, and be like this:

_begin_file_
name=Bob
Age=47
Sex=M

The columns are now rows. I was thinking about rewriting the query as something like this

Select "name=" & name as "_begin_file_" from clients
Where name = [whatever] UNION ALL
Select "Age=" & age as "_begin_file_" from clients
Where name = [whatever] UNION ALL
Select "Sex=" & sex as "_begin_file_" from clients
Where name = [whatever]

Would them export one record per time.
Is there a better way?

Upvotes: 0

Views: 194

Answers (1)

Nexus
Nexus

Reputation: 821

What if you have 50 records , you want to create 50 text files ? That's crazy. Let create an button click event that might help.Let say you still export to Excel

Private Sub CommandExport_Click()
Dim db As Database
Dim rec1 As Recordset
Dim xlFile As Object
Dim xlWorkBook As Object
Dim xlActiveWkb As Object
Dim xlActiveSheet As Object
Dim Col As Integer

Set db = CurrentDb
Set xlFile = CreateObject("Excel.Application")
Set xlWorkBook = xlFile.Workbooks.Add
Set xlActiveWkb = xlFile.Application.ActiveWorkBook

xlFile.Visible = True
xlActiveWkb.Sheets.Add
xlActiveWkb.Worksheets(1).Name = "Test"

Set xlActiveSheet = xlActiveWkb.Worksheets("Test")
Set rec1 = db.OpenRecordset("clients") 

rec1.MoveFirst
Col = 1
while not rec1.eof
    xlActiveSheet.Cells(1, Col).Value = "_begin__file_"
    xlActiveSheet.Cells(2, Col).Value = "Name=" & rec1![Name]
    xlActiveSheet.Cells(3, Col).Value = "Age=" & rec1![Age]
    xlActiveSheet.Cells(4, Col).Value = "Sex=" & rec1![Sex]
    Col =Col +1
    rec1.MoveNext
wend

Set xlSheet = Nothing
Set xlWorkBook = Nothing
Set xlActiveWkb = Nothing
rec1.Close
db.Close
Set rec1 = Nothing
Set db = Nothing
End Sub

Upvotes: 1

Related Questions