Control Excel from Access

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

I am trying to open an Excel file from Access, make changes, save the file,
and close the file. When I run the code in Excel, it works fine. I figured
if I can run this in Access, I can learn something new and save myself some
time during my increasingly busy days.

Option Compare Database

Sub Rep()
'Open file
Dim xlapp As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlws As Excel.Worksheet
Dim xlrng As Excel.Range
Dim adors As ADODB.Recordset
Dim xlFile As String
Dim xlSaveFile As String
xlFile = "C:\Documents and Settings\ryan\Desktop\Mark\Mark - Union Crosstab
Rep.xls"
xlSaveFile = "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
xlrng.CopyFromRecordset adors
adors.Close
Set adors = Nothing
xlwb.SaveAs xlSaveFile
xlwb.Close
Set xlrng = Nothing
Set xlws = Nothing
Set xlwb = Nothing
xlapp.Quit
Set xlapp = Nothing
End Sub


The error message is: Run-time error 1004
Method 'Columns' of object '_Global failed.

Any ideas?

Thanks so much,
Ryan--
 
I am trying to open an Excel file from Access, make changes, save the file,
and close the file.  When I run the code in Excel, it works fine.  I figured
if I can run this in Access, I can learn something new and save myself some
time during my increasingly busy days.

Option Compare Database

Sub Rep()
'Open file
Dim xlapp As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlws As Excel.Worksheet
Dim xlrng As Excel.Range
Dim adors As ADODB.Recordset
Dim xlFile As String
Dim xlSaveFile As String
xlFile = "C:\Documents and Settings\ryan\Desktop\Mark\Mark - Union Crosstab
Rep.xls"
xlSaveFile = "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
xlrng.CopyFromRecordset adors
adors.Close
Set adors = Nothing
xlwb.SaveAs xlSaveFile
xlwb.Close
Set xlrng = Nothing
Set xlws = Nothing
Set xlwb = Nothing
xlapp.Quit
Set xlapp = Nothing
End Sub

The error message is: Run-time error 1004
Method 'Columns' of object '_Global failed.

Any ideas?

Thanks so much,
Ryan--
Click to expand...


You say "When I run the code in Excel, it works fine." - well, of
course it does, because the Excel library is loaded and all the
objects you are using are already instantiated. But if you're running
Access, the Excel object model is not available for manipulation
unless you create a reference to Excel somehow, by using either
getting or creating a new Excel instance.

You probably should read some of Dev's simple examples of Excel
automation to see all the parts your code is missing, which is here:

http://www.mvps.org/access/modules/mdl0006.htm

here's the code... what the heck...

Code Courtesy of
'Dev Ashish
'
Sub sTestXL()
Dim objXL As Object
Dim strWhat As String, boolXL As Boolean
Dim objActiveWkb As Object

If fIsAppRunning("Excel") Then
Set objXL = GetObject(, "Excel.Application")
boolXL = False
Else
Set objXL = CreateObject("Excel.Application")
boolXL = True
End If

objXL.Application.workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkBook

With objActiveWkb
.Worksheets(1).Cells(1, 1) = "Hello World"
strWhat = .Worksheets(1).Cells(1, 1).value
End With

objActiveWkb.Close savechanges:=False

If boolXL Then objXL.Application.Quit

Set objActiveWkb = Nothing: Set objXL = Nothing
MsgBox strWhat
End Sub
 
Thanks to CraigH, finally got it working!!

Solution is here
http://www.microsoft.com/office/com...76a-54c5398c90c6&cat=&lang=en&cr=US&sloc=&p=1


Thanks again CraigH,
Ryan---
--
RyGuy


Piet Linden said:
I am trying to open an Excel file from Access, make changes, save the file,
and close the file. When I run the code in Excel, it works fine. I figured
if I can run this in Access, I can learn something new and save myself some
time during my increasingly busy days.

Option Compare Database

Sub Rep()
'Open file
Dim xlapp As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlws As Excel.Worksheet
Dim xlrng As Excel.Range
Dim adors As ADODB.Recordset
Dim xlFile As String
Dim xlSaveFile As String
xlFile = "C:\Documents and Settings\ryan\Desktop\Mark\Mark - Union Crosstab
Rep.xls"
xlSaveFile = "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
xlrng.CopyFromRecordset adors
adors.Close
Set adors = Nothing
xlwb.SaveAs xlSaveFile
xlwb.Close
Set xlrng = Nothing
Set xlws = Nothing
Set xlwb = Nothing
xlapp.Quit
Set xlapp = Nothing
End Sub

The error message is: Run-time error 1004
Method 'Columns' of object '_Global failed.

Any ideas?

Thanks so much,
Ryan--
Click to expand...


You say "When I run the code in Excel, it works fine." - well, of
course it does, because the Excel library is loaded and all the
objects you are using are already instantiated. But if you're running
Access, the Excel object model is not available for manipulation
unless you create a reference to Excel somehow, by using either
getting or creating a new Excel instance.

You probably should read some of Dev's simple examples of Excel
automation to see all the parts your code is missing, which is here:

http://www.mvps.org/access/modules/mdl0006.htm

here's the code... what the heck...

Code Courtesy of
'Dev Ashish
'
Sub sTestXL()
Dim objXL As Object
Dim strWhat As String, boolXL As Boolean
Dim objActiveWkb As Object

If fIsAppRunning("Excel") Then
Set objXL = GetObject(, "Excel.Application")
boolXL = False
Else
Set objXL = CreateObject("Excel.Application")
boolXL = True
End If

objXL.Application.workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkBook

With objActiveWkb
.Worksheets(1).Cells(1, 1) = "Hello World"
strWhat = .Worksheets(1).Cells(1, 1).value
End With

objActiveWkb.Close savechanges:=False

If boolXL Then objXL.Application.Quit

Set objActiveWkb = Nothing: Set objXL = Nothing
MsgBox strWhat
End Sub
Click to expand...
 
Back
Top