Column..._Global failed error

S

syuhas

I'm currently creating an excel document via an Access 2003 Module.
I write the xls fine yet when I go to format the xls I get th
following
"Method 'Columns' of object '_Global' failed"... How do I create an
object to use the column?

If you sroll down you'll find this:

'''''''''''''''''''''' Here's where it break
'''''''''''''''''''''''''''''''''

that's where the problems begin... Any help will be greatl
appreciated.

Thanks.

Sean

Option Compare Database

Sub CreateReport()

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Sheets(1)
xlApp.Visible = True

With xlSheet

.Range("A1") = "Analyst"
.Range("B1") = "Program Description"
.Range("C1") = "$"
.Range("D1") = "PCO/OS"
.Range("E1") = "Program Manager/OS"
.Range("F1") = "Best Value Methodology"
.Range("G1") = "RFP Release"
.Range("H1") = "Comp Range"
.Range("I1") = "Decision Brief"
.Range("J1") = "Award"
.Range("K1") = "Status/Comments(FPR, award, protest, etc"
.Range("L1") = "Estimated SS Facility Need Date"

End With

Dim strSQL As String
Dim strSQL2 As String
Dim RS As ADODB.Recordset
Set RS = New ADODB.Recordset
Dim RS2 As ADODB.Recordset
Set RS2 = New ADODB.Recordset

Dim RecCount As Integer

strSQL = "SELECT [All Data].SSEA, [All Data].ProgramName
[All Data].EstDollars, [All Data].PCO, [All Data].PCOOS, [All Data].PM
[All Data].PMOS, [All Data].BVSSEA, [All Data].BVMETHOD, [Al
Data].RFPDate, [All Data].MSCompRang, [All Data].MSDecision, [Al
Data].AdateAward, [All Data].ID, CurrentStatus.Date
CurrentStatus.Status, CurrentStatus.strCurrent FROM [All Data] INNE
JOIN CurrentStatus ON [All Data].Id = CurrentStatus.ProgramID WHER
(((CurrentStatus.strCurrent)='-1'));"
RS.Open strSQL, CurrentProject.Connection, adOpenStatic

'CurrRec = RS("ID")
'strSQL2 = "SELECT Date_tbl.* FROM Date_tbl WHER
(((Date_tbl.ProgramID)=" & CurrRec & "))"
'And ((Date_tbl.Current=true));"
'RS2.Open strSQL2, CurrentProject.Connection, adOpenStatic


RecCount = RS.RecordCount


Dim CoreCells(10)

CoreCells(0) = "A"
CoreCells(1) = "B"
CoreCells(2) = "C"
CoreCells(3) = "D"
CoreCells(4) = "E"
CoreCells(5) = "F"
CoreCells(6) = "G"
CoreCells(7) = "H"
CoreCells(8) = "I"
CoreCells(9) = "J"
CoreCells(10) = "K"


k = 2

For i = 1 To RecCount

Dim CellValue(10)

CellValue(0) = RS![SSEA]
CellValue(1) = RS![ProgramName]
CellValue(2) = RS![EstDollars]
CellValue(3) = RS![PCO] & "/" & RS![PCOOS]
CellValue(4) = RS![PM] & "/" & RS![PMOS]
CellValue(5) = RS![BVMethod]
CellValue(6) = RS![RFPDate]
CellValue(7) = RS![MSCompRang]
CellValue(8) = RS![MSDecision]
CellValue(9) = RS![AdateAward]
CellValue(10) = RS![Status]



For j = 0 To UBound(CoreCells)

Cellval = CoreCells(j)
k

With xlSheet
.Range(Cellval)
CellValue(j)
End With


Next j




RS.MoveNext
k = k + 1
Next i

Set RS = Nothing

'''''''''''''''''''''' Here's where it break
'''''''''''''''''''''''''''''''''

Columns("A:N").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

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.EntireColumn.AutoFit
Columns("N").ColumnWidth = 60.11
Columns("N").WrapText = True

Range("A1").Select


'''''''''''''''''''''''''''''''''''''''''''''''''''''''

End Su
 
J

Jim Cone

Sean,

"Columns("A:N").Select" should be xlSheet.Columns("A:N").Select"
Also, the Excel constants are not available to Access.
So xlNone should be xlApp.xlNone
You may have other problems in the code. When automating Excel...

Every reference to Excel should be qualified with the appropriate
object reference. If you don't then "orphan" references can be
created that will prevent the Excel app from closing.

For example...
Range("A1").Select should be...xlSheet.Range("A1").Select
Range(Cells(1, 5), Cells(1, 10)) should be...
xlSheet.Range(xlSheet.Cells(1, 5), xlSheet.Cells(1, 10))

Do not use Selection, ActiveCell, ActiveWorkbook etc, use your object references.
For instance...
Columns("A:N").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Should be...
xlSheet.Columns("A:N").Borders(xlDiagonalDown).LineStyle = xlApp.xlNone
xlSheet.Columns("A:N").Borders(xlDiagonalUp).LineStyle = xlApp.xlNone

It is usually safer to avoid the use of "With".

Every object reference should be Set to Nothing before exiting the Sub.

Regards,
Jim Cone
San Francisco, USA


"syuhas"
wrote in message
I'm currently creating an excel document via an Access 2003 Module.
I write the xls fine yet when I go to format the xls I get the
following
"Method 'Columns' of object '_Global' failed"... How do I create an
object to use the column?

If you sroll down you'll find this:
'''''''''''''''''''''' Here's where it breaks
that's where the problems begin... Any help will be greatly
appreciated.
Thanks.
Sean

Option Compare Database
Sub CreateReport()
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Sheets(1)
xlApp.Visible = True
With xlSheet

Range("A1") = "Analyst"
Range("B1") = "Program Description"
Range("C1") = "$"
Range("D1") = "PCO/OS"
Range("E1") = "Program Manager/OS"
Range("F1") = "Best Value Methodology"
Range("G1") = "RFP Release"
Range("H1") = "Comp Range"
Range("I1") = "Decision Brief"
Range("J1") = "Award"
Range("K1") = "Status/Comments(FPR, award, protest, etc"
Range("L1") = "Estimated SS Facility Need Date"

End With

Dim strSQL As String
Dim strSQL2 As String
Dim RS As ADODB.Recordset
Set RS = New ADODB.Recordset
Dim RS2 As ADODB.Recordset
Set RS2 = New ADODB.Recordset

Dim RecCount As Integer

strSQL = "SELECT [All Data].SSEA, [All Data].ProgramName,
[All Data].EstDollars, [All Data].PCO, [All Data].PCOOS, [All Data].PM,
[All Data].PMOS, [All Data].BVSSEA, [All Data].BVMETHOD, [All
Data].RFPDate, [All Data].MSCompRang, [All Data].MSDecision, [All
Data].AdateAward, [All Data].ID, CurrentStatus.Date,
CurrentStatus.Status, CurrentStatus.strCurrent FROM [All Data] INNER
JOIN CurrentStatus ON [All Data].Id = CurrentStatus.ProgramID WHERE
(((CurrentStatus.strCurrent)='-1'));"
RS.Open strSQL, CurrentProject.Connection, adOpenStatic
'CurrRec = RS("ID")
'strSQL2 = "SELECT Date_tbl.* FROM Date_tbl WHERE
(((Date_tbl.ProgramID)=" & CurrRec & "))"
'And ((Date_tbl.Current=true));"
'RS2.Open strSQL2, CurrentProject.Connection, adOpenStatic
RecCount = RS.RecordCount

Dim CoreCells(10)
CoreCells(0) = "A"
CoreCells(1) = "B"
CoreCells(2) = "C"
CoreCells(3) = "D"
CoreCells(4) = "E"
CoreCells(5) = "F"
CoreCells(6) = "G"
CoreCells(7) = "H"
CoreCells(8) = "I"
CoreCells(9) = "J"
CoreCells(10) = "K"
k = 2

For i = 1 To RecCount
Dim CellValue(10)
CellValue(0) = RS![SSEA]
CellValue(1) = RS![ProgramName]
CellValue(2) = RS![EstDollars]
CellValue(3) = RS![PCO] & "/" & RS![PCOOS]
CellValue(4) = RS![PM] & "/" & RS![PMOS]
CellValue(5) = RS![BVMethod]
CellValue(6) = RS![RFPDate]
CellValue(7) = RS![MSCompRang]
CellValue(8) = RS![MSDecision]
CellValue(9) = RS![AdateAward]
CellValue(10) = RS![Status]

For j = 0 To UBound(CoreCells)
Cellval = CoreCells(j) &
k

With xlSheet
Range(Cellval) =
CellValue(j)
End With

Next j

RS.MoveNext
k = k + 1
Next i

Set RS = Nothing

'''''''''''''''''''''' Here's where it breaks
'''''''''''''''''''''''''''''''''

Columns("A:N").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

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.EntireColumn.AutoFit
Columns("N").ColumnWidth = 60.11
Columns("N").WrapText = True

Range("A1").Select
'''''''''''''''''''''''''''''''''''''''''''''''''''''''

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Hlp! Adv Filter - Non-Contiguous rows don't copy 11
Loops through Sheets but doesn't work. 5
Macro to sort, format, sum, a dynamic set of data 2
How to rewrite this code? (Second half) 1
Simplify 1
How to rewrite this code? (first half) 1
Page Break???????? 11
Hiding rows and columns in a procedure 3

Top