How convert exported file that contains just one record to an Excel file but in one column

I'm exporting a query result to an Excel file via:

DoCmd.TransferSpreadsheet acExport, 10, "qryNotes", Environ("userprofile") & "\Desktop\Notes.xlsx", True

Open in new window


But I need to somehow end up with the field in columns in the Excel file rather than rows.

Is this even possible?

--Steve
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
You could try to modify the method shown here:

http://www.experts-exchange.com/Database/MS_Access/Q_28467368.html

/gustav
0
SteveL13Author Commented:
Well, I'm stuck again.  Here is what the export looks like now:

Current
And here's what I want it to look like:

Would like
0
Helen FeddemaCommented:
This can be done, but not using TransferSpreadsheet (unless you do the fields to columns conversion first in Access, and then export the resulting table).  Here is some code to export from a one-row table directly to an Excel workbook:
Public Sub FieldsToColumns()
'Created by Helen Feddema 30-Jul-2014
'Last modified by Helen Feddema 12-Aug-2014

On Error GoTo ErrorHandler

   Dim appExcel As New Excel.Application
   Dim wkb As Excel.Workbook
   Dim rst As DAO.Recordset
   Dim fld As DAO.Field
   Dim intFieldCount As Integer
   Dim intCount As Integer
   Dim strFieldName As String
   Dim varFieldValue As Variant
   Dim sht As Excel.Worksheet
   Dim intRow As Integer
   Dim strRange As String
   Dim rng As Excel.Range
   
   Set rst = CurrentDb.OpenRecordset("tblEmployeesOneRecord")
   intFieldCount = rst.Fields.Count
   Debug.Print "No. of Fields: " & intFieldCount
   Set wkb = appExcel.Workbooks.Add
   Set sht = wkb.Sheets(1)
   intRow = 1
   
   For intCount = 0 To intFieldCount - 1
      strFieldName = rst.Fields(intCount).Name
      Debug.Print "Field name: " & strFieldName
      strRange = "A" & CStr(intRow)
      Set rng = sht.Range(strRange)
      rng.Value = strFieldName
      varFieldValue = rst.Fields(intCount)
      Debug.Print "Field value: " & varFieldValue
      strRange = "B" & CStr(intRow)
      Set rng = sht.Range(strRange)
      rng.Value = varFieldValue
      intRow = intRow + 1
   Next intCount
   
   appExcel.Visible = True
   
ErrorHandlerExit:
   Set appExcel = Nothing
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in FieldsToColumns procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Helen FeddemaCommented:
Don't try this with an OLE object field -- it won't work.
0
Gustav BrockCIOCommented:
If you don't want to mess with Excel, you can use a temp table with two text fields:

    FieldName
    FieldValue

Call it tblNotes.
Then run this code to transpose the fields and values:
Public Function FillNotes()

    Dim dbs         As DAO.Database
    Dim rstSource   As DAO.Recordset
    Dim rstTarget   As DAO.Recordset
    Dim fld         As DAO.Field
    
    Set dbs = CurrentDb
    Set rstSource = dbs.OpenRecordset("qryNotes")
    Set rstTarget = dbs.OpenRecordset("tblNotes")
    
    While rstTarget.EOF = False
        rstTarget.Delete
        rstTarget.MoveNext
    Wend
    
    If rstSource.RecordCount = 1 Then
        For Each fld In rstSource.Fields
            rstTarget.AddNew
                rstTarget.Fields(0).Value = fld.Name
                rstTarget.Fields(1).Value = CStr(Nz(fld.Value))
            rstTarget.Update
        Next
    End If
    rstTarget.Close
    rstSource.Close
    
    Set rstTarget = Nothing
    Set rstSource = Nothing
    
    Set dbs = Nothing
    
    ' Run export.
    DoCmd.TransferSpreadsheet acExport, 10, "tblNotes", Environ("userprofile") & "\Desktop\Notes.xlsx", True
    
End Function

Open in new window

/gustav
0
SteveL13Author Commented:
Helen,

The code is working perfectly.  The module does what I wanted it to do.   Now I need the function to save the Excel file to the user's desktop automatically.  Something like this I guess.  But I don't know how to code it and then where to put the code.  

DoCmd.TransferSpreadsheet acExport, 10, "I don't know what to put here", Environ("userprofile") & "\Desktop\Notes.xlsx", True
0
SteveL13Author Commented:
One more thing... I need it to enter only the most recent record in the Excel file.
0
Gustav BrockCIOCommented:
But you had only one record ...

Else use Top 1 in your query and sort on your "recent" field:

Select Top 1 * From tblYourTable Order By Id Desc

/gustav
0
SteveL13Author Commented:
Gustav:  Works.  But I still need to somehow save the Excel file to the user's desktop automatically.  Something like this I guess.  But I don't know how to code it and then where to put the code.  

 DoCmd.TransferSpreadsheet acExport, 10, "I don't know what to put here", Environ("userprofile") & "\Desktop\Notes.xlsx", True
0
Helen FeddemaCommented:
For saving the workbook, you need to specify a path and file name, and possibly a file format (Excel version).  What should they be?  Let me know, and I will post revised code.
0
Gustav BrockCIOCommented:
No, that's from Access. In Excel, you will add a code line to SaveAs the workbook (can't recall the full syntax off head).

/gustav
0
SteveL13Author Commented:
Wouldn't it be:

Environ("userprofile") & "\Desktop\Notes.xlsx"
0
Helen FeddemaCommented:
Which code are you using?
0
Helen FeddemaCommented:
Here is my code, modified to save the workbook to the user's desktop.  Modify the user name and file name as you wish.

Public Sub FieldsToColumns()
'Created by Helen Feddema 30-Jul-2014
'Last modified by Helen Feddema 12-Aug-2014

On Error GoTo ErrorHandler

   Dim appExcel As New Excel.Application
   Dim wkb As Excel.Workbook
   Dim rst As DAO.Recordset
   Dim fld As DAO.Field
   Dim intFieldCount As Integer
   Dim intCount As Integer
   Dim strFieldName As String
   Dim varFieldValue As Variant
   Dim sht As Excel.Worksheet
   Dim intRow As Integer
   Dim strRange As String
   Dim rng As Excel.Range
   Dim strXLFile As String
   
   Set rst = CurrentDb.OpenRecordset("tblEmployeesOneRecord")
   intFieldCount = rst.Fields.Count
   Debug.Print "No. of Fields: " & intFieldCount
   Set wkb = appExcel.Workbooks.Add
   Set sht = wkb.Sheets(1)
   intRow = 1
   
   For intCount = 0 To intFieldCount - 1
      strFieldName = rst.Fields(intCount).Name
      Debug.Print "Field name: " & strFieldName
      strRange = "A" & CStr(intRow)
      Set rng = sht.Range(strRange)
      rng.Value = strFieldName
      varFieldValue = rst.Fields(intCount)
      Debug.Print "Field value: " & varFieldValue
      strRange = "B" & CStr(intRow)
      Set rng = sht.Range(strRange)
      rng.Value = varFieldValue
      intRow = intRow + 1
   Next intCount
   
   strXLFile = "C:\Users\Helen Feddema\Desktop\From Access.xlsx"
   wkb.SaveAs FileName:=strXLFile
   appExcel.Visible = True
   
ErrorHandlerExit:
   Set appExcel = Nothing
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in FieldsToColumns procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.