Access - Excel format

[hyperionaxa]

I am very new to all this technical stuff, but I am trying to format an excel spreadsheet via access vba but getting an error. Can some one please advice?
My code is:

Private Sub Command3_Click()
DoCmd.Hourglass True
DoCmd.SetWarnings False
    'On Error GoTo ErrHandler:
' *************** RUN EUR RI DATA RECON ********************
 
    Dim objXL As Object
    Dim strWin As String
    Dim sglRep As Single
    Dim intPos As Integer
    'Set newName = Sheets("Data")
    'newName.Visible
 
    strFile = "P:\test.xls"
 
    If strFile = "" Then Exit Sub
 
    'Saves changes and closes the speradsheet
    Set objXL = GetObject(strFile)
    objXL.Close savechanges:=True
 
    'Transfers the data to the Excel tabs depending on the file being updated
    'DoCmd.TransferSpreadsheet acExport, 8, "tbl_2004 UW Data", strFile, True, "2004 UWY"
    DoCmd.TransferSpreadsheet acExport, 8, "test", strFile, True, "test"
 
 
 
    Set objXL = GetObject(strFile)
    objXL.Application.Visible = True
 
    strWin = strFile
    intPos = 1
 
    'Finds name of window
    Do Until intPos = 0
 
        intPos = InStr(strWin, "\")
        strWin = Right(strWin, Len(strWin) - intPos)
 
    Loop
 
    'Makes spreadsheet current and unhidden
    objXL.Parent.Windows(strWin).Activate
    objXL.Parent.Windows(strWin).Visible = True
 
    'Sets file to read only
    'If strFile = "P:\AgedDebtors\CashDataMaster.xls" Then
     '   objXL.Application.DisplayAlerts = False
      '  objXL.ChangeFileAccess Mode:=xlReadOnly
        'Sheets("Data").Select
        'Cells.Select
    'Cells.EntireColumn.AutoFit
    'Range("A1").Select
    'Range("A1:R48").Sort Key1:=Range("O2"), Order1:=xlDescending, Header:= _
     '   xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
      '  DataOption1:=xlSortNormal
    'End If
 
    strFile = ""
    strWin = ""
    sglRep = 0
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
 
    Range("a:c").select
 
    'MsgBox "Run Complete"
 
'ErrHandler:
    'MsgBox (Err.Source & " - " & Err.Description)
End Sub

Thanks in advance.

[puppydogbuddy]

To use Linq's expression, Carnac the Magnificent is not available at this site, and we can't read your mind....LOL! Please provide more details. What is the error you are getting? Could the error be related to some of the code you have commented out?

[Stewart Ross]

Hi. I have added code tags before and after your code section, which makes it much easier to refer to specific lines.

As PDog has said, you really must tell us what is going wrong and what kind of error message you are getting - we should not be guessing.

As an observation, I can say for certain that the commented out code at lines 50-54 will not function reliably in Access, as you are not using the automation object variable you have defined (objXL) to communicate with the Excel instance. Referring to ranges and so on works within Excel itself without qualification by the worksheet object, but not when running as automation code from Access.

ALL references to ranges and so on will need to be qualified with the current object and worksheet variables. For example,

ObjXL.ActiveSheet.Range("A1").Select

and not

Range("A1").Select

As an aside, it is much more efficient not to select ranges and cells before setting them to specific values or formats; refer directly to the range or cell object to set its properties.

Many of these problems arise when using the Macro Recorder in Excel to record actions. The code recorded is neither truly portable when used in an automation context nor particularly efficient.

-Stewart