none
Run-time error '1004': VBA

    Question

  • Hi,
    I hope someone can help with this, I get Run-time error '1004': Application-defined or object-defined error when running my code in
    Access. on the highlighted line.  The code is

    Dim db As DAO.Database

    Set db = CurrentDb

    Dim appexcel As Object

    Set appexcel = CreateObject("Excel.Application")

    appexcel.Visible=True 'You can see the excel s/s 'test99

    appexcel.Workbooks.Add

    appexcel.Sheets("Sheet1").Name= "SalesDetail"

    appexcel.Sheets.Add

    Dim sqlSalesDetail As String

    Dim rsSalesDetail As recordset

    Set rsSpclPayments = db.OpenRecordset(rsSalesDetail)

    sqlSalesDetail= "transform sum(TblCommissionSalesDetailInfoTemp.ActualACV) as totalcharge,

    select
    TblCommissionSalesDetailInfoTemp.CustomerName,
    TblCommissionSalesDetailInfoTemp.CalenderMonth,
    "FROM
    TblCommissionSalesDetailInfoTemp;"
    " GROUP BY
    CustomerName,CalenderMonth,

    "
    PIVOT TblCommissionSalesDetailInfoTemp.TargetName;"

    DoComd.RunSQLsqlSalesDetail

    .Sheets("SalesDetail").Select

    .Range("A1").Select

    'write clumn headers
    c= 1

    For
    F = 0 To rsSalesDetail.Fields.Count - 1

    'OnError Resume Next

    .Sheets("SalesDetail").Cells(1,c + F).Formula =rsSalesDetail.Fields(F).Name

    On
    Error GoTo 0

    Next F

    .Range("a2").CopyFromRecordset rsSalesDetail

    Set rsSalesDetail = Nothing

    Please can someone help
    by the way I have also definded the path for exporting the excel fime and it works... only having problems with this specific code.

    Many thanks


    Thursday, July 19, 2012 7:20 PM

Answers

  • I'd use

    Dim appExcel As Object
    Dim wbk As Object
    Dim wsh As Object
    Set appExcel = CreateObject("Excel.Application")
    appExcel.Visible=True 'You can see the excel s/s 'test99
    Set wbk = appExcel.Workbooks.Add
    Set wsh = wbk.Worksheets(1)
    wsh.Name= "SalesDetail"


    Regards, Hans Vogelaar

    • Proposed as answer by Mike7952 Thursday, July 19, 2012 7:57 PM
    • Marked as answer by Leo_GaoModerator Thursday, July 26, 2012 1:26 AM
    Thursday, July 19, 2012 7:31 PM
  • Change the line

    rng.Sort Key1:=Range("LastCol"), Order1:=xlDescending , Header = xlYes

    to

    rng.Sort Key1:=.Cells(1, LastCol), Order1:=xlDescending , Header = xlYes


    Regards, Hans Vogelaar

    Friday, July 20, 2012 4:23 PM

All replies

  • I'd use

    Dim appExcel As Object
    Dim wbk As Object
    Dim wsh As Object
    Set appExcel = CreateObject("Excel.Application")
    appExcel.Visible=True 'You can see the excel s/s 'test99
    Set wbk = appExcel.Workbooks.Add
    Set wsh = wbk.Worksheets(1)
    wsh.Name= "SalesDetail"


    Regards, Hans Vogelaar

    • Proposed as answer by Mike7952 Thursday, July 19, 2012 7:57 PM
    • Marked as answer by Leo_GaoModerator Thursday, July 26, 2012 1:26 AM
    Thursday, July 19, 2012 7:31 PM
  • thanks.. I will try and let you u know if it works

    Best

    Thursday, July 19, 2012 9:33 PM
  • It worked !!

    Thanks... I used :

     Dim rsPivot As Recordset
      Set rsPivot = db.OpenRecordset(sqlSalesDetail)
     
    With appexcel
    appexcel.Sheets.Add
    appexcel.Sheets("Sheet5").Name = "SalesDetail"
    appexcel.Sheets.Add
    End With

        .Sheets("SalesDetail").Select
        .Range("A1").Select
         'write column headers
       
       c = 1
       For F = 0 To rsPivot.Fields.Count - 1
       On Error Resume Next
       .Sheets("SalesDetail").Cells(1, c + F).Formula = rsSalesDetail.Fields(F).Name
       On Error GoTo 0
     Next F

    .Range("a2").CopyFromRecordset rsSalesDetail

    Set rsPivot = Nothing
     Call FormatHeaders(appexcel)

    Friday, July 20, 2012 2:19 PM
  • Thanks :)
    Friday, July 20, 2012 2:19 PM
  • Hi All,

    I am now getting a new error :(

    Anyone can have a look and may be help ...

    I have this code to sort values in the last column of worksheet ("Pivot")... but I get error on .Range("rng").Select .... any ideas how to get around this?

    With Sheets("Pivot")
          LastRow = .Cells(.Cells.Rows.Count, "a").End(xlUp).Row()
          LastCol = .Cells(1, .Cells.Columns.Count).End(xlToLeft).Column()
          Dim rng As Range
          Set rng = .Range(.Cells(1, "a"), .Cells(LastRow, LastCol))
        
          .Range("rng").Select
          .Range("B2").Activate
          .Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

          End With

    Thanks all,

    Friday, July 20, 2012 2:21 PM
  • kitkat,

    You dont need the "" in Range("rng").Select rng is object not a string. You can due rng.Select

    Friday, July 20, 2012 2:42 PM
  • You can only select cells in the active worksheet. But in most situations, it isn't necessary to select cells in VBA.

    Try this:

        With Sheets("Pivot")
           LastRow = .Cells(.Cells.Rows.Count, "a").End(xlUp).Row()
           LastCol = .Cells(1, .Cells.Columns.Count).End(xlToLeft).Column()
           Dim rng As Range
           Set rng = .Range(.Cells(1, "a"), .Cells(LastRow, LastCol))
           rng.Sort Key1:=Range("B1"), Order1:=xlDescending
       End With


    Regards, Hans Vogelaar

    Friday, July 20, 2012 2:48 PM
  • Thanks, I tried but get Run Time error 438: Object doesnt support this property or method on  below:

    I am going to look at the rest of my code now....

    something doesnt seem right,,, will keep u updated

    Friday, July 20, 2012 3:12 PM
  • OK ... I know the problem....

    I need to sort the data based on the values in the last column, by the way my sheet has header row;

    Trying this now... but get error again

     With Sheets("Pivot")
           LastRow = .Cells(.Cells.Rows.Count, "a").End(xlUp).Row()
           LastCol = .Cells(1, .Cells.Columns.Count).End(xlToLeft).Column()
           Dim rng As Range
           Set rng = .Range(.Cells(1, "a"), .Cells(LastRow, LastCol))
           rng.Sort Key1:=Range("LastCol"), Order1:=xlDescending , Header = xlYes
           End With

    Friday, July 20, 2012 3:24 PM
  • Change the line

    rng.Sort Key1:=Range("LastCol"), Order1:=xlDescending , Header = xlYes

    to

    rng.Sort Key1:=.Cells(1, LastCol), Order1:=xlDescending , Header = xlYes


    Regards, Hans Vogelaar

    Friday, July 20, 2012 4:23 PM
  • Thanks....

    I am going to sort my data in the query ... instead of going to the worksheet and sorting values from there.

    I added order by to my sql code but I keep getting error. I added a new question so hopefully will get help to solve it somehow.

    Theoretically order by should work without problem....

    Friday, July 20, 2012 4:33 PM