excel automation

[sam_01]

hello freinds !!!
i am trying to automate excel from access !!

i am generating excel sheets from access and i want to format the excel sheets after they are exported from the table in access

i tried using the excel automation like get object as well as createobject but none worked for me !!
i am getting the error as file(.xls) not found despite it exist in the path dir.

can any one of u guys help me out with the code

any reply would be of grt help !!
thanks in advance

 

[Rabbie]

I do something similar without any problems in A2003. It might be helpful if you can post your database so we can see exactly what you have done and see why it does not work as expected.

 

[qdogfball]

this is code for setting up column widths after exporting to Excel

' Set the column widths in Excel

    Dim WS As Worksheet
    Dim Ws_Count As Integer
    Dim I As Integer
    Dim xlapp As Object
    Dim xlwkb As Object
    
    Set xlapp = CreateObject("Excel.Application")
    xlapp.Application.Visible = False
    Set xlwkb = xlapp.Workbooks.Open("c:\Temp\Assignments.xls")
    
    Ws_Count = xlwkb.Worksheets.Count

    For I = 1 To Ws_Count
      
      Sheets(I).Activate
      ' changes to Landscape orientation in Excel
        Sheets(I).PageSetup.Orientation = xlLandscape
        
        Columns("G:G").Select
        Selection.ColumnWidth = 16
        Columns("f:f").Select
        Selection.ColumnWidth = 11
        Columns("c:c").Select
        Selection.ColumnWidth = 11
        Columns("d:d").Select
        Selection.ColumnWidth = 6
        Columns("I:I").Select
        Selection.ColumnWidth = 12
        Columns("E:E").Select
        Selection.ColumnWidth = 9
        Columns("H:H").Select
        Selection.ColumnWidth = 9
        Columns("J:J").Select
        Selection.ColumnWidth = 6
        Columns("K:K").Select
        Selection.ColumnWidth = 9
        Columns("A:A").Select
        Selection.ColumnWidth = 6
        Columns("b:b").Select
        Selection.ColumnWidth = 6
        Columns("l:l").Select
        Selection.ColumnWidth = 13
        
    Next I
    
    xlapp.ActiveWorkbook.Save
    xlapp.ActiveWorkbook.Close
    xlapp.Quit
    
    Set WS = Nothing
    Set xlapp = Nothing
    Set xlwkb = Nothing