Control Excel from Access

[ryguy7272]

I posted here about a week ago and haven't had time to come back to this
issue since then. I'm posting again so my question goes to the top of the
'list' and I'll link the old post to the new if I can find a solution via
this new post. Basically I have a few small subs that I use in Excel. I am
trying to figure out a way to just stay in Access, to save time, but do my
operations in Excel. Here is the code (with references to Excel):

Option Compare Database

Sub Rep()
'Open file

Dim objXL As Object
Dim xlFile As Object
Dim strWhat As String, boolXL As Boolean
Dim objActiveWkb As Object

Set objXL = CreateObject("Excel.Application")
boolXL = True
objXL.Application.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkbook

xlFile = "C:\Documents and Settings\ryan\Desktop\Mark\Mark - Union Crosstab
Rep.xls"

'Begin formatting
Columns("F:F").Select
Selection.Cut
Columns("H:H").Select
ActiveSheet.Paste
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
Selection.Font.Bold = True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A2").Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6,
7), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Call Calc1
End Sub


Sub Calc1()

Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-4]="""",RC[-1]=0),""Goal is
Zero"",IF(RC[-4]="""",((RC[-3]+RC[-2])/RC[-1]),""""))"
Range("H3").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 7).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Selection.Style = "Percent"
Columns("E:G").Select
Selection.Style = "Currency"

Call PlaceBottomDoubleBorderLines1
End Sub


Sub PlaceBottomDoubleBorderLines1()

Dim C As Range
For Each C In Range("A1").Resize(Cells(Rows.Count, "A").End(xlUp).Row)
If C.Font.Bold Then
With C.Resize(, 8).Borders(xlEdgeBottom)
..LineStyle = xlDouble
..Weight = xlThick
..ColorIndex = xlAutomatic
End With
End If
Next

'Save changes and close file
objActiveWkb.Close savechanges:=True

If boolXL Then objXL.Application.Quit
Set objActiveWkb = Nothing: Set objXL = Nothing
MsgBox strWhat
End Sub

I do have a reference set to Excel!! Nevertheless, the code fails on this
line:
xlFile = "C:\Documents and Settings\ryan\Desktop\Mark\Mark - Union Crosstab
Rep.xls"

The message that I get is:
Run-time error ‘91’
Object variable or With block not set

What do I need to do to get this working?

Thanks,
Ryan---

 

[CraigH]

I don't know all the ins and outs of Excel Object Model (couldn't figure why
the cut wouldn't work- see code) but I can get it to do what you want and
give you a good start. I will also comment inline about areas you need to be
aware of in your understanding of Automation.

But to anwser "What do I need to do to get this working?" +"From Access" a
couple opening comments:

1. Remember you are in the Access and not Excel environment so you have to
make sure you are refering to the particular Object (workbook, Worksheet,
range etc...) anytime you do something. [Examples in code]

2. Instead of using CreateObject - directly name the object this way you get
the use of Intellisense. NOTE - Make sure that under Tool-References you get
your version of Microsoft Excel.

Here is the start

Option Compare Database
Option Explicit ' Use this to make sure your variables are defined

' One way to be able to use these objects throghout the Module is to Declare
them
' Here and not in a Sub

Private objExcel As Excel.Application
Private xlWB As Excel.Workbook
Private xlWS As Excel.Worksheet

Sub Rep()

Dim strFile as String


strFile = "C:\Documents and Settings\ryan\Desktop\Mark\Mark - Union Crosstab
Rep.xls"

' Opens Excel and makes it Visible
Set objExcel = New Excel.Application
objExcel.Visible = True

'Opens up the Workbook
Set xlWB = objExcel.Workbooks.Open(strFile)

'Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.
Set xlWS = xlWB.ActiveSheet
'Set xlWS = xlWB("Sheet2")

With xlWS ' You are now working with the Named file and the named worksheet
'Begin formatting
' I had an error with the Cut and PasteSpecial so use the copy and then
clear the Area
.Range("F1:F5").Select
.Range("F1:F5").Copy
.Range("H1:H5").Activate 'This
.Range("H1:H5").PasteSpecial
.Range("F1:F5").Clear

End With

....
'Do Close and Cleanup
End Sub

Try doing this small step before you go on to the rest of your code (most
needs to be changed)

Hope this is a good start for you

I posted here about a week ago and haven't had time to come back to this
issue since then. I'm posting again so my question goes to the top of the
'list' and I'll link the old post to the new if I can find a solution via
this new post. Basically I have a few small subs that I use in Excel. I am
trying to figure out a way to just stay in Access, to save time, but do my
operations in Excel. Here is the code (with references to Excel):

Option Compare Database

Sub Rep()
'Open file

Dim objXL As Object
Dim xlFile As Object
Dim strWhat As String, boolXL As Boolean
Dim objActiveWkb As Object

Set objXL = CreateObject("Excel.Application")
boolXL = True

' Having the Option Explicit you will have to decalre the boolXL -
Helps with Intellisense

objXL.Application.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkbook

' You were adding a new workbook then trying to open a saved workbook
' But you were not really opening it with that line you were assigning a
string to an Object

xlFile = "C:\Documents and Settings\ryan\Desktop\Mark\Mark - Union Crosstab
Rep.xls"

'Begin formatting

' in the Excel Environment this may work but from Access you have to refer
to object as I do with the xlWS

Columns("F:F").Select
Selection.Cut
Columns("H:H").Select
ActiveSheet.Paste
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
Selection.Font.Bold = True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A2").Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6,
7), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Call Calc1
End Sub


Sub Calc1()

Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-4]="""",RC[-1]=0),""Goal is
Zero"",IF(RC[-4]="""",((RC[-3]+RC[-2])/RC[-1]),""""))"
Range("H3").Select

' Only the A1 is selected here - don't think this is going to do what
you want it

Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 7).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Selection.Style = "Percent"
Columns("E:G").Select
Selection.Style = "Currency"

Call PlaceBottomDoubleBorderLines1
End Sub


Sub PlaceBottomDoubleBorderLines1()

Dim C As Range
For Each C In Range("A1").Resize(Cells(Rows.Count, "A").End(xlUp).Row)
If C.Font.Bold Then

' Havn't checked but this may be a problem

With C.Resize(, 8).Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End If
Next

' I would close out in the original calling Proc but that is my style

 

[Pendragon]

Actually, ryan, the way you are defining and calling the Excel objects --
late binding -- is the preferred method by the gurus of Access from what I
have read in these forums.

I don't know all the ins and outs of Excel Object Model (couldn't figure why
the cut wouldn't work- see code) but I can get it to do what you want and
give you a good start. I will also comment inline about areas you need to be
aware of in your understanding of Automation.

But to anwser "What do I need to do to get this working?" +"From Access" a
couple opening comments:

1. Remember you are in the Access and not Excel environment so you have to
make sure you are refering to the particular Object (workbook, Worksheet,
range etc...) anytime you do something. [Examples in code]

2. Instead of using CreateObject - directly name the object this way you get
the use of Intellisense. NOTE - Make sure that under Tool-References you get
your version of Microsoft Excel.

Here is the start

Option Compare Database
Option Explicit ' Use this to make sure your variables are defined

' One way to be able to use these objects throghout the Module is to Declare
them
' Here and not in a Sub

Private objExcel As Excel.Application
Private xlWB As Excel.Workbook
Private xlWS As Excel.Worksheet

Sub Rep()

Dim strFile as String


strFile = "C:\Documents and Settings\ryan\Desktop\Mark\Mark - Union Crosstab
Rep.xls"

' Opens Excel and makes it Visible
Set objExcel = New Excel.Application
objExcel.Visible = True

'Opens up the Workbook
Set xlWB = objExcel.Workbooks.Open(strFile)

'Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.
Set xlWS = xlWB.ActiveSheet
'Set xlWS = xlWB("Sheet2")

With xlWS ' You are now working with the Named file and the named worksheet
'Begin formatting
' I had an error with the Cut and PasteSpecial so use the copy and then
clear the Area
.Range("F1:F5").Select
.Range("F1:F5").Copy
.Range("H1:H5").Activate 'This
.Range("H1:H5").PasteSpecial
.Range("F1:F5").Clear

End With

...
'Do Close and Cleanup
End Sub

Try doing this small step before you go on to the rest of your code (most
needs to be changed)

Hope this is a good start for you

I posted here about a week ago and haven't had time to come back to this
issue since then. I'm posting again so my question goes to the top of the
'list' and I'll link the old post to the new if I can find a solution via
this new post. Basically I have a few small subs that I use in Excel. I am
trying to figure out a way to just stay in Access, to save time, but do my
operations in Excel. Here is the code (with references to Excel):

Option Compare Database

Sub Rep()
'Open file

Dim objXL As Object
Dim xlFile As Object
Dim strWhat As String, boolXL As Boolean
Dim objActiveWkb As Object

Set objXL = CreateObject("Excel.Application")
boolXL = True

' Having the Option Explicit you will have to decalre the boolXL -
Helps with Intellisense

objXL.Application.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkbook

' You were adding a new workbook then trying to open a saved workbook
' But you were not really opening it with that line you were assigning a
string to an Object

xlFile = "C:\Documents and Settings\ryan\Desktop\Mark\Mark - Union Crosstab
Rep.xls"

'Begin formatting

' in the Excel Environment this may work but from Access you have to refer
to object as I do with the xlWS

Columns("F:F").Select
Selection.Cut
Columns("H:H").Select
ActiveSheet.Paste
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
Selection.Font.Bold = True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A2").Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6,
7), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Call Calc1
End Sub


Sub Calc1()

Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-4]="""",RC[-1]=0),""Goal is
Zero"",IF(RC[-4]="""",((RC[-3]+RC[-2])/RC[-1]),""""))"
Range("H3").Select

' Only the A1 is selected here - don't think this is going to do what
you want it

Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 7).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Selection.Style = "Percent"
Columns("E:G").Select
Selection.Style = "Currency"

Call PlaceBottomDoubleBorderLines1
End Sub


Sub PlaceBottomDoubleBorderLines1()

Dim C As Range
For Each C In Range("A1").Resize(Cells(Rows.Count, "A").End(xlUp).Row)
If C.Font.Bold Then

' Havn't checked but this may be a problem

With C.Resize(, 8).Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End If
Next

' I would close out in the original calling Proc but that is my style

'Save changes and close file
objActiveWkb.Close savechanges:=True

If boolXL Then objXL.Application.Quit
Set objActiveWkb = Nothing: Set objXL = Nothing
MsgBox strWhat
End Sub

I do have a reference set to Excel!! Nevertheless, the code fails on this
line:
xlFile = "C:\Documents and Settings\ryan\Desktop\Mark\Mark - Union Crosstab
Rep.xls"

The message that I get is:
Run-time error ‘91’
Object variable or With block not set

What do I need to do to get this working?

Thanks,
Ryan---

 

[CraigH]

That is true if you are developing for a distributed app or in situation
where you have different versions of the "Reference".

See:
http://support.microsoft.com/default.aspx/kb/245115

Although from a Word MVP here is an easy to read list
http://word.mvps.org/fAQs/InterDev/EarlyvsLateBinding.htm

Even

http://www.granite.ab.ca/access/latebinding.htm

who proposses Late binding suggests:

"You'll want to install the reference if you are programming or debugging
and to use the handy object IntelliSense code expansion while in the VBA
Editor. Then once the code is running smoothly change the conditional
compiler constant. "

So at least start with the "Early Binding" and decide later or use the
coding technique from the above Latebinding.htm

 

[ryguy7272]

Wicked cool, CraigH!! I thought it was something like that, but not quite
sure what. I should be fine from this point on!! I’m over the hurdle;
thanks for the push!!

Regards,
Ryan---