Question Access to Excel Macro problems

D

dazstarr

Registered User.
Local time
Today, 02:22
Joined
Mar 19, 2007
Messages
132
Afternoon Folks

Please can somebody help me with a problem.

I have an Access form with a command button - the button runs a access macro which uses the transfer spreadsheet command to export a table into an excel workbook. All this works fine but I would like to apply some formatting to the spreadsheet.

I have recorded a macro within excel which applies the formatting but not sure how to piece the two together. I would like to combine the functions to opening Excel, opening the sheet and then run the formatting macro. As this is a new excel sheet, excel doesnt save my macro. The only way i can get this to run is to open another Excel workbook which has my macro saved and run it from there.

Below is the code I have used from my access module:

/code
Sub OpenSpecific_xlFile()
' Late Binding (Needs no reference set)
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim sPath As String


' Create a new Excel instance
Set oXL = CreateObject("Excel.Application")


' Only XL 97 supports UserControl Property
On Error Resume Next
oXL.UserControl = True
On Error GoTo 0


' Full path of excel file to open
On Error GoTo ErrHandle
sFullPath = "C:\CLA\CLA_Pupils"


' Open it
With oXL
.Visible = True
.Workbooks.Open (sFullPath)
End With


ErrExit:
Set oXL = Nothing
Exit Sub

ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit
End Sub
\code



And here is the code behind my access form button:

/code
Private Sub Command59_Click()
On Error GoTo Err_Command59_Click
Dim stDocName As String
Dim oApp As Object
stDocName = "CLA Pupil Export"
DoCmd.RunMacro stDocName

Call OpenSpecific_xlFile

Exit_Command59_Click:
Exit Sub
Err_Command59_Click:
MsgBox Err.Description
Resume Exit_Command59_Click
End Sub
\code

Also here is my code for my excel macro:

/code
Cells.Select
Selection.Columns.AutoFit
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Application.Goto Reference:="R1C1"
ActiveCell.FormulaR1C1 = "Original Pupil Data"
Range("A2").Select
Application.Goto Reference:="R1C27"
ActiveCell.FormulaR1C1 = "PLASC"
Range("AA2").Select
Application.Goto Reference:="R1C49"
ActiveCell.FormulaR1C1 = "FFT Estimates"
Range("AW2").Select
Application.Goto Reference:="R1C134"
ActiveCell.FormulaR1C1 = "Keys To Success"
Range("ED2").Select
Application.Goto Reference:="R1C154"
ActiveCell.FormulaR1C1 = "QCI Data"
Range("EX2").Select
Application.Goto Reference:="R1C178"
ActiveCell.FormulaR1C1 = "Absence Data"
Range("FV2").Select
Application.Goto Reference:="R1C1:R1C26"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Application.Goto Reference:="R1C27:R1C48"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Application.Goto Reference:="R1C49:R1C133"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Application.Goto Reference:="R1C134:R1C153"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Application.Goto Reference:="R1C154:R1C177"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Application.Goto Reference:="R1C178:R1C189"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Selection.Font.Bold = True
Selection.RowHeight = 30
Application.Goto Reference:="R1C1"
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Range("AA1:AV1").Select
With Selection.Interior
.ColorIndex = 39
.Pattern = xlSolid
End With
Range("AW1:EC1").Select
With Selection.Interior
.ColorIndex = 39
.Pattern = xlSolid
End With
Range("AW1:EC1").Select
Selection.Interior.ColorIndex = 36
Range("ED1:EW1").Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
Range("EX1:FU1").Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
Range("FV1:GG1").Select
With Selection.Interior
.ColorIndex = 45
.Pattern = xlSolid
End With
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("AA1:AV1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("AW1:EC1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("ED1:EW1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("EX1:FU1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("FV1:GG1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("A1:Z1").Select
End Sub
/code

Any help would be greatly appreciated

Cheers
Daz
 
You can run excel VBA commands from Access VBA. Try looking up those commands, then Access will do the work. There maybe some changes to the code needed, but they should be minimal.

P.S. If you would like people to look through your code, please use the code window as below:
Code: 
example of a code window
It makes it much easier to read and people will be more willing to help you.
 
Thanks Yarp

Will do.
 
And a visual on the code window:
codetag001.png


And you need to get rid of this

oXL.UserControl = True

because it takes control away from you and gives it to the user. And the note about it only valid for Access 97 is incorrect. It is completely true for ALL versions of Access 97 and above.

And third, you need to read this before going further. You are making a big coding mistake by trying to use code that is not tied to an instantiated object.
 
Many thanks Bob

I will look into your advice.

Thanks again
Daz
 
You must log in or register to reply here.

Similar threads

Z
Attempts to make my code more efficient don't work
Replies
5
Views
475
Zedster
Z
G
Extract correct leave time
Replies
2
Views
555
goncalo
G
L
Retrieve excel from access (vol 4) , extended format , alternative color
Replies
7
Views
747
arnelgp
arnelgp
D
Solved Formatting Excel File from Access
Replies
5
Views
907
Eugene-LS
Eugene-LS
M
Solved Access to Excel link
Replies
8
Views
915
bastanu
bastanu

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom