Help for noobie trying to run an Excel Macro in Access

[Loaded_bloke]

Hello all,
I know that there have been alot of posts on the above subjecy but being a noob i dont really understand what they are on about. Basically, i have xported a file from access to excel, i then have a little macro that formats the sheet form me, this is saved in my personal file. Is there a way that i can set a form button up in access top run this macro. I have tried a few of the suggestions made on this board but they dont seem to have worked, a majority of them say that they cannot find personal.xls "macro name". Any help would be appreciated as i am at the point of throwing my comp out the window.

Many Thanks
Loaded_bloke

 

[Norie]

What are you trying to use the macro to do?

Are you trying to format an Access table with it?

 

[Loaded_bloke]

no sorry, i am exporting a spreadsheet from access to excel. The macro is set up in excel to format the spreadsheet. i was wondering if there is a way to run the macro straight from access rather than having to go into excel and run it from there

thanks

 

[Loaded_bloke]

help help help

Hello again guys, this is the code that i have come up with from looking at various posts, when i try to run this this appears

"COMPILE ERROR" "INVALID OUTSIDE PROCEDURE"

Option Compare Database
Option Explicit


Dim oExcel As Excel.Application
Dim myExcelFile As String

myExcelFile = "G:\Reservations\dave\Credit Control\Credit Controls.xls"
Set oExcel = New Excel.Application
oExcel.Workbooks.Open FileName:=myExcelFile
oExcel.Visible = True

'Sub CreditCheck()
' CreditCheck Macro
' Macro recorded 04/04/2005 by Carryl Morgan
'

'
Rows("1:1").RowHeight = 39.6
Range("G2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""Exceeded"""
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
End With
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""OK"""
With Selection.FormatConditions(2).Font
.Bold = True
.Italic = False
End With
Selection.FormatConditions(2).Interior.ColorIndex = 35
Selection.Copy
Columns("G:G").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Font.Bold = True
Columns("C:E").Select
Selection.NumberFormat = "$#,##0.00"
Range("C2:F1215").Select
Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Select
Cells.EntireColumn.AutoFit
Rows("1:1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells.Select
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("B11").Select
Selection.End(xlDown).Select
Range("B1215").Select
Selection.End(xlUp).Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P of &N"
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.393700787401575)
.RightMargin = Application.InchesToPoints(0.393700787401575)
.TopMargin = Application.InchesToPoints(1.5748031496063)
.BottomMargin = Application.InchesToPoints(0.393700787401575)
.HeaderMargin = Application.InchesToPoints(0.78740157480315)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 1200
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 20
End With
End Sub


...
End With
...

If Not oExcel Is Nothing Then
oExcel.Application.Quit
Set oExcel = Nothing
End If



As you can probably tell, i havent got a clue what i am doing so any help woul;d be greatly appreciated.

Many Thanks
loaded

 

[Norie]

Your on the right track.

Take a look at this.

I've not done all your code as there is a lot of redundacy in it.

But hopefully this will point you in the right direction.

Sub CreditCheck()
 'CreditCheck Macro
 'Macro recorded 04/04/2005 by Carryl Morgan

Dim oExcel As Excel.Application
Dim oExcelWB As Excel.Workbook
Dim oExcelWS As Excel.Worksheet
Dim myExcelFile As String

    myExcelFile = "G:\Reservations\dave\Credit Control\Credit Controls.xls"
    Set oExcel = New Excel.Application
    Set oExcelWB = oExcel.Workbooks.Open(FileName:=myExcelFile)
    Set oExcelWS = oExcelWB.ActiveSheet
    oExcel.Visible = True
    
    With oExcelWS
    .Rows("1:1").RowHeight = 39.6
        With .Range("G2")
            With .FormatConditions
                .Delete
                
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
                Formula1:="=""Exceeded"""
                .FormatConditions(1).Font.Bold = True
                .FormatConditions(1).Interior.ColorIndex = 3
        
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
                Formula1:="=""OK"""
                .FormatConditions(2).Font.Bold = True
        
                .FormatConditions(2).Interior.ColorIndex = 35
                .Copy
            End With
        End With
        .Columns("G:G").PasteSpecial Paste:=xlFormats
        With .Cells
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlBottom
            .Orientation = 0
            .ShrinkToFit = False
            .MergeCells = False
        End With
    End With
    If Not oExcel Is Nothing Then
        oExcel.Application.Quit
        Set oExcel = Nothing
    End If
End Sub

 

[Loaded_bloke]

Thank you

Many Thanks for your help. I hope that i am not making a pain of myself. When i run the module it opens the correct spreadsheet although the macro does not run. I get an error message to this effect "Run time error 438" "Object doesnt support this property or method"

Thanks
Loaded

 

[Norie]

On which line.

 

[Loaded_bloke]

Hello Norie, once again thanks for your help. The error message is highlighting this line:

.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""Exceeded"""

Many Thanks
Loaded

 

[Norie]

My fault - I referenced FormatConditions twice.

Try this

Option Explicit

Sub CreditCheck()
 'CreditCheck Macro
 'Macro recorded 04/04/2005 by Carryl Morgan

Dim oExcel As Excel.Application
Dim oExcelWB As Excel.Workbook
Dim oExcelWS As Excel.Worksheet
Dim myExcelFile As String

    myExcelFile = "G:\Reservations\dave\Credit Control\Credit Controls.xls"
    Set oExcel = New Excel.Application
    Set oExcelWB = oExcel.Workbooks.Open(Filename:=myExcelFile)
    Set oExcelWS = oExcelWB.ActiveSheet
    oExcel.Visible = True
    
    With oExcelWS
        .Rows("1:1").RowHeight = 39.6
        With .Range("G2")
            With .FormatConditions
                .Delete
                
                .Add Type:=xlCellValue, Operator:=xlEqual, _
                Formula1:="=""Exceeded"""
                .Item(1).Font.Bold = True
                .Item(1).Interior.ColorIndex = 3
        
                .Add Type:=xlCellValue, Operator:=xlEqual, _
                Formula1:="=""OK"""
                .Item(2).Font.Bold = True
        
                .Item(2).Interior.ColorIndex = 35
                .Copy
            End With
        End With
        .Columns("G:G").PasteSpecial Paste:=xlFormats
        With .Cells
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlBottom
            .Orientation = 0
            .ShrinkToFit = False
            .MergeCells = False
        End With
    End With
    If Not oExcel Is Nothing Then
        oExcel.Application.Quit
        Set oExcel = Nothing
    End If
End Sub

 

[Loaded_bloke]

All was going well until this error appeared

"Compile error - method or data member not found"

Thank you for your help with this. hope i am not being too much of a pain

The above error appeared in the following line

Application.CutCopyMode = False

loaded