Help with Error when trying to copy and transpose data from Access to Excel

[Lavenderchan]

Hello,

I am trying to copy records from a form then export them to excel and transpose the data. I am getting the Runtime Error "1004" application defind or obejct-defined error. In the code that is highlight in yellow. The line is also posted here ".ActiveSheet.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True". What am I doing wrong?
Private Sub Command1166_Click()
DoCmd.RunCommand acCmdSelectAllRecords 'Select all the records-ie including filtred record
DoCmd.RunCommand acCmdCopy 'Copy the selected record
Dim xlApp As Object

Set xlApp = CreateObject("Excel.Application") 'create an excel application object
With xlApp
.Workbooks.Add 'add new workbook in the excel
[highlight #FCE94F].ActiveSheet.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True[/highlight]


.Cells.Select 'now select all cells in excel
.Cells.EntireColumn.WrapText = True 'wrap text in all cells
.Columns("B:E").ColumnWidth = 9
.Columns("A:A").ColumnWidth = 43
'the above does this: More spaces needed in columns N&M while less space needed in A & G to I

.Cells.Rows.AutoFit 'applying auto fit feature for rows
.ActiveSheet.Name = "Budget"

'***************************************************************************************
'Now loop through the rows starting from row 1 to 19 which is A1 to S1 and apply formating as below
Dim i As Integer
For i = 1 To 6
.Cells(1, i).Font.Bold = True
.Cells(1, i).Font.ColorIndex = 1
.Cells(1, i).Interior.ColorIndex = 42

Next 'end of loop
'****************************************************************************************

.worksheets(1).Cells(2, 2).Activate ' make cell B2 as the active cell
.ActiveWindow.FreezePanes = True 'Now freezepanes from the active cell B2
.Visible = True
.Range("a1").Select 'If for some reason if other cells are selected please select A1 as am now done.

End With
export_Click_Exit:
Exit Sub
export_Click_Err:
MsgBox Error$
Resume export_Click_Exit
End Sub

 

[combo]

PasteSpecial with those arguments work only for copies between worksheet data within the same excel instance. It also requires reference to excel library (to have proper excel constants values).
Try instead:
[tt].ActiveSheet.PasteSpecial Format:="Tekst", Link:=False, DisplayAsIcon:=False[/tt]
to helper sheet and transpose in next step in excel.
Better, instead [tt]ActiveSheet[/tt] in this case use [tt]Worksheets(1)[/tt].
Excel has also [tt]CopyFromRecordset[/tt] method, but it does not include headers.

combo

 

[Lavenderchan]

I am trying to export data from access to excel and tranpose the data. Combo, when I tried the .ActiveSheet.PasteSpecial Format:="Tekst", Link:=False, DisplayAsIcon:=False
However it came back with an error as well. The error was Run time error 1004 pastspecial method of worksheet class failed.

 

[SkipVought]

Try...
[tt]
.ActiveSheet.Cells(1,1).PasteSpecial Format:="Tekst", Link:=False, DisplayAsIcon:=False
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Lavenderchan]

Thank you for you reply I am now geting the error Runtime Error "1004" application defind or obejct-defined error when I used .ActiveSheet.Cells(1,1).PasteSpecial Format:="Tekst", Link:=False, DisplayAsIcon:=False. What could be causing it.

 

[combo]

To debug:
1. try to work with well defined objects:
[pre]Set xlWbk = xlApp.Workbooks.Add 'add new workbook in the excel
With xlWbk
.Worksheets(1).PasteSpecial Format:="Tekst", Link:=False, DisplayAsIcon:=False ' try with .Cells(1,1) inside too[/pre]
2. try to work with visible excel:
Set xlApp = CreateObject("Excel.Application") 'create an excel application object
[pre]xlApp.Visible = True[/pre]
3. check ability to paste: break code before creating excel, do it manually and try to paste special.

You may have problems with objects hierarchy in the second part of your code, the natural order: Application - Workbook - Worksheet - Range (and Cells that return Range), skipping some items in this path, if works, uses active objects.


combo

 

[Lavenderchan]

Could a tool reference be interfering with this?

 

[combo]

What tool?

Concerning paste special, modify Format argument, it's local:
[tt].Worksheets(1).PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False[/tt]
For me the same does [tt]Format:=3[/tt].

PasteSpecial applied to range (cell) works for excel-excel copies, this includes transposition. PasteSpecial fork workbook works with different set of arguments and is used for other application-excel copying. See:

https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/worksheet-pastespecial-method-excel

https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/range-pastespecial-method-excel

and older:

https://msdn.microsoft.com/en-us/library/office/aa195818(v=office.11).aspx

combo