Lavenderchan
Technical User
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
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