Changing the Paste Orientation of a Transfer Spreadsheet

[doma23]

Hi, I'm copy pasting the question that I've found on the net while I was trying to find the answer on the same problem, but didn't manage:

I have the followig TransferSpreadsheet Statement that moves the results of a query to an Excel Workbook which works fine. Problem is there are a lot of fields in the query and it is diffficult for people to read that far across. Is there a way to modify this so that it exports the data vertically say a1:b whatever instead of a1: EN2
Sort of a Paste Special transpose.

Thanks!

Here is the code:

DoCmd.TransferSpreadsheet acExport, 8, "A_EXPORT", str, True, ""

[doma23]

Ok, either nobody understand the question or there is no answer (more likely). I was hoping that there are some other solutions that may do the job.

Ok, just to be sure, I'll try to be more clear:

Is it possible to write code in Access that will
execute the query, export it to Excel, open the Excel file, then perform a copy/cut and paste with the transpose option?

[doma23]

OK. I have found the solution and I would like to share it just in case somebody runs on the same problem:

Private Sub cmdExportXLS_Click()
 
Const EXPORT_DATA_PATH = "C:\AccessProject\Output\Data.xls"
 
Call Transposer("query-name", "table-name")
DoCmd.TransferSpreadsheet acExport, , "table-name", EXPORT_DATA_PATH, False, "worksheet-name"
DoCmd.DeleteObject acTable, "table-name"
 
End Sub

Transposer is a function that takes one table (or a query) and then makes another table and transpose all the data from the previous table into new one.
I've found it on the Microsoft website.

TRANSPOSER FUNCTION:

Public Function Transposer(strSource As String, strTarget As String)
 
   Dim db As DAO.Database
   Dim tdfNewDef As DAO.TableDef
   Dim fldNewField As DAO.Field
   Dim rstSource As DAO.Recordset, rstTarget As DAO.Recordset
   Dim i As Integer, j As Integer
 
   On Error GoTo Transposer_Err
 
   Set db = CurrentDb()
   Set rstSource = db.OpenRecordset(strSource)
   rstSource.MoveLast
 
   ' Create a new table to hold the transposed data.
   ' Create a field for each record in the original table.
   Set tdfNewDef = db.CreateTableDef(strTarget)
   For i = 0 To rstSource.RecordCount
      Set fldNewField = tdfNewDef.CreateField(CStr(i + 1), dbText)
      tdfNewDef.Fields.Append fldNewField
   Next i
   db.TableDefs.Append tdfNewDef
 
   ' Open the new table and fill the first field with
   ' field names from the original table.
   Set rstTarget = db.OpenRecordset(strTarget)
   For i = 0 To rstSource.Fields.Count - 1
      With rstTarget
        .AddNew
        .Fields(0) = rstSource.Fields(i).Name
        .Update
      End With
   Next i
 
   rstSource.MoveFirst
   rstTarget.MoveFirst
   ' Fill each column of the new table
   ' with a record from the original table.
   For j = 0 To rstSource.Fields.Count - 1
      ' Begin with the second field, because the first field
      ' already contains the field names.
      For i = 1 To rstTarget.Fields.Count - 1
         With rstTarget
            .Edit
            .Fields(i) = rstSource.Fields(j)
            rstSource.MoveNext
            .Update
         End With
 
      Next i
      rstSource.MoveFirst
      rstTarget.MoveNext
   Next j
 
   db.Close
 
   Exit Function
 
Transposer_Err:
 
   Select Case Err
      Case 3010
         MsgBox "The table " & strTarget & " already exists."
      Case 3078
         MsgBox "The table " & strSource & " doesn't exist."
      Case Else
         MsgBox CStr(Err) & " " & Err.Description
   End Select
 
   Exit Function
 
End Function

[ahmedtharwat19]

doma23, Good soulotion

i will try it...

Thank You..

(Medo)

[NeoPa]

Doma,

That's a tidy solution, but not how I would have done it myself (though now that solution is available I'm not so sure in future).

An alternative would be to export the results into a spreadsheet then, using Application Automation, handle the Copy and Paste Special / Transpose in your code.

[doma23]

@NeoPa
Aha, so it's possible to insert Excel VBA code into Access sub.
Yeah, I've thought that something like that might be possible, but didn't know that it actually is.
Great, tnx for the pointing to another possible solution.
I think it's quite good, but I guess it requires a little bit more VBA knowledge, as I don't know how would I actually write the code.

[NeoPa]

No worries Doma. Glad to help where I can.

Can you tell me exactly what you would need help with. I have added a post to that linked article after your reply, as I reread it and felt it was necessary. I don't know if that's all you need though. There are various examples included already, specifically one directed at using Excel. Can you explain what else would help, if indeed the latest post doesn't resolve your current difficulty.

[doma23]

Ok, just to be easier, I'm copy-pasting the code:

' Requires a Reference to Microsoft Excel 8.0 Object Library or Higher 
' In VBE, goto Tools->References... and select it from the list 
Private Const conAppNotRunning As Long = 429 
 
Private Sub ExportToExcel() 
    ' Variable Declarations 
    Dim objExcel As Excel.Application 
 
    ' If Excel is open, use GetObject, otherwise create a new Excel object 
    On Error Resume Next 
    Set objExcel = GetObject(, "Excel.Application") 
    If Err = conAppNotRunning Then Set objExcel = New Excel.Application 
 
    With objExcel 
        ' Adds a new workbook to the Excel environment 
        .Workbooks.Add 
 
        ' Excel VBA Code goes here 
 
        ' Causes the Excel window to become visible 
        .Visible = True 
    End With 
End Sub 

So first, the title of the sub confuses me a little, as how I understood it - it doesn't export anything to Excel, it just controls Excel application.

I guess first I would need to use docmd.transferspreadsheet to export the query to excel and then call your ExcelAutomation sub.

So, this is how it should look like:

Private Sub cmdExportXLS_Click() 
 
DoCmd.TransferSpreadsheet acExport, , "table-name", "C:\access\data.xls", False, "worksheet-name" 
Call ExportToExcel  
 
End Sub 

Also, docmd.transferspreadsheet would just tranfer it to xls file, and afterward it would close xls file immediately. So how would ExportToExcel sub know which xls file to use? I'm looking at the code, and I don't quite understand it.

Of course, instead of ' Excel VBA Code goes here in ExportToExcel sub, I would put the code that would handle copy/paste/transpose.
I don't know how to code that. I was referring on that in my previous reply.

Also, I'm not sure would I need to use Range() property in this case. If I would need to, it would be a problem as you wrote that it would fail due to the absence of the reference object.

So this is how I see it, but there is a possibility that I got the idea completely wrong, as I'm quite new to Access and VBA world.

Looking forward to get your feedback, NeoPa.
Cheers!

[NeoPa]

There are a couple of points to bear in mind here :

1. The code example you refer to is a simple template to give an idea of the structure of using Excal code within Access. It is not an implementation of anything really other than the basics of the structure. The name of the procedure is pretty random, and only reflects your requirement by accident (or because that's probably the most popular use for Excel Automation anyway).
2. The example code was submitted later by another member (as noted), so I can't absolutely guarantee that the code works well. It seems fine, but not checked fully by me. One caveat I would mention is that using :
   

   Set objExcel = New Excel.Application

   is not a technique I was aware of nor have I checked it. I would use the function CreateObject() myself. That is not to say it won't work. Just that I haven't tested that approach myself.
3. Application Automation is not too simple a concept. I brought it to your attention because the description of your problem was specific enough to indicate it was the sort of thing you had in mind. It is quite advanced, and has various complications involved (not easy to develop), but when mastered can provide a great deal of power to your projects. If this sounds like something you're still interested in then I'm happy to lead you forward. I don't plan simply to do it all for you though. This must be something you feel comfortable taking on, rather than a neat trick you're happy for someone-else to do for you.

So, to answer a couple of points from your last post :

1. If you export the data to a spreadsheet in your Access code, you would need to control and remember where that file is, and use that to control the file to open from within your embedded Excel code.
2. You first need to develop the code you will use to do the transposing within Excel. It is pretty straightforward. If you struggle with this then a good technique is to record a macro then do the task manually. This will create some (pretty basic) code to do the job for you. You may need to tweak it somewhat, but it will provide all the basic techniques required.

[doma23]

OK, now it's more clear when I know it's merely a template.
Like you said - it's not simple concept. And yes, I've had something like this in mind when I was searching for a solution, but honestly I've thought it would be easier.

Luckily, as I've already showed, I've came accross another pretty neat and maybe much easier solution (giving that the transpose function code is provided, as it is in this case).
I'm sure there are various situations in which it won't be possible to use that type of solutions and in which it will be required to use Excel automation.

But since I'm still not on advanced level (started using access two months ago) and since I have a deadline to build this application, this is not something I would feel comfortable to dive in to right now.
There are still some other less advanced concepts that I must master first and as far as the application I'm developing goes, I think that the transpose function and docmd.transferspreadsheet is more or less everything I will need to communicate with Excel.

But it's definitely very valuable to know that something like this does exist, so if the problem which require this type of solution arises one day I will know where to look and what to search for.

Therefore NeoPa, thank you very much for your effort and willingness to teach.

[NeoPa]

It's been a pleasure Doma.

As you know, you know where we are now should you need us again :)