Run-time error '1004'

[coachdan]

I have some code in Access that takes the results of a query and sends it to Excel in the proper format, but it throwing the following error:

Run-time error '1004'
Application-defined or object-defined error


The error is returned on the following line of code:

ExSheet.Range(Alpha & Col).EntireColumn.ColumnWidth = 10


Alpha is a variable that was setup to keep up with the alphabetic part of the cell address.

coachdan32

http://www.malehsbaseball.com

 

[HarleyQuinn]

How about something like:

ExSheet.Columns(Alpha & ":" & Alpha).Select
Selection.ColumnWidth = 10.00

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[coachdan]

I don't believe that is the problem because I reuse this code on several other reports in the same database and it doesn't have a problem with the syntax on them. I didn't write the code, but inherited it. Does the syntax mean the width of each column is 10? Or is it saying to add 10 columns? Because the reports that have this error all have more than 10 columns.

coachdan32

http://www.malehsbaseball.com

 

[PHV]

In the Alpha column are any cell merged ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[coachdan]

No, they are not merged and my idea that the columns total more than 10 is not it either. I will post the entire code for this report, maybe there is something there I am missing.

Private Sub cmdDeliverRep_Click()

Dim DBase As Database
Dim ExApp As Object
Dim ExBook As Object
Dim ExSheet As Object
Dim RecRaw As Recordset
Dim Qdata As Recordset
Dim Row As Integer
Dim Col As Integer
Dim Center As String
Dim I1 As Integer
Dim I2 As Integer
Dim I3 As Integer
Dim I4 As Integer
Dim Q As Integer
Dim x As Integer
Dim QDef As QueryDef
Dim Ranking As Long
Dim Alpha
Dim TaskCount

'Set the database used to the current db
Set DBase = CurrentDb()


'Create an Excel spreadsheet, add a tab to it and name it
Set ExApp = CreateObject("Excel.Application")
Set ExBook = ExApp.Workbooks.Add
ExApp.Visible = True


Set ExSheet = ExBook.Worksheets.Add

Sheets("Sheet4").Name = "Delivery Report"


'Open recordsets from the Access db
Set Rec = DBase.QueryDefs("qryReportDelivery_Crosstab").OpenRecordset
Set Rec2 = DBase.QueryDefs("qryReportDelivery_Crosstab_homesite").OpenRecordset


TaskCount = 0 'Initialize a counter to use to cycle through the tasks listed
EndCol = 0 'Not sure what this counter is used for
SiteCount = 0 'Initialize a counter to use to cycle through the sites
Alpha = "A" 'Still not clear, but know that it is used to handle cell addresses
Row = 1 'Initializing the row to start at 1
Col = 1 'Initializing the column to start at 1


' Headers for Excel Report
ExSheet.Cells(Row, Col) = "Task"
ExSheet.Range(Alpha & Col).EntireColumn.ColumnWidth = 40
Col = Col + 1
Alpha = NumToAlpha(Col)

' Print out the names of all the sites dynamically
Do While Not Rec2.EOF

ExSheet.Cells(Row, Col) = Rec2("Homesite")
ExSheet.Range(Alpha & Col).EntireColumn.ColumnWidth = 10
SiteCount = SiteCount + 1
Col = Col + 2
Alpha = NumToAlpha(Col)

Rec2.MoveNext
Loop

ExSheet.Cells(Row, Col) = "Grand Total"
ExSheet.Range(Alpha & Col).EntireColumn.ColumnWidth = 10



Col = 1
Row = Row + 1


Do While Not Rec.EOF
Set Rec2 = DBase.QueryDefs("qryReportDelivery_Crosstab_homesite").OpenRecordset

ExSheet.Cells(Row, Col) = Rec("Task")
TaskCount = TaskCount + 1
Col = Col + 2


Do While Not Rec2.EOF
ExSheet.Cells(Row, Col) = Rec(Rec2("Homesite"))


Col = Col + 2
Rec2.MoveNext
Loop
EndCol = Col

ExSheet.Cells(Row, Col) = Rec("TotalOfHours")



ExSheet.Range("K1").EntireColumn.Hidden = True

Col = 1
Row = Row + 1

Rec.MoveNext
Loop





Col = 1
ExSheet.Cells(Row, Col) = "Grand Total"

Col = Col + 2

Alpha = "C"
Alpha2 = "B"
Col2 = 2

SiteCountTemp = SiteCount + 1
Do While SiteCountTemp > 0

ExSheet.Cells(Row, Col) = "=Sum(" & Alpha & "2:" & Alpha & (TaskCount + 1) & ")"


Col2 = Col2 + 2
Alpha2 = NumToAlpha(Col2)

Col = Col + 2
SiteCountTemp = SiteCountTemp - 1
ColNum = AlphaToNum(Alpha)
ColNum = ColNum + 2
Alpha = NumToAlpha(ColNum)

Loop


Row = 2
Col = 2
Alpha = "C"
TotalLine = TaskCount + 2
TaskCountTemp = TaskCount
SiteCountTemp = SiteCount + 1


Do While SiteCountTemp > 0

Do While TaskCountTemp > 0
ExSheet.Cells(Row, Col) = "=(" & Alpha & Row & "/" & Alpha & TotalLine & ")"
ExSheet.Cells(Row, Col).NumberFormat = "0.00%"

Row = Row + 1
TaskCountTemp = TaskCountTemp - 1

Loop

ExSheet.Cells(Row, Col) = "1.0"
ExSheet.Cells(Row, Col).NumberFormat = "0.00%"
TaskCountTemp = TaskCount


Row = 2
Col = Col + 2
Alpha = NumToAlpha(Col + 1)
SiteCountTemp = SiteCountTemp - 1


Loop


'Formatting
Col = 3
Alpha = NumToAlpha(Col)
SiteCountTemp = SiteCount + 2


Select Case SiteCountTemp

Case 1
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True

Case 2
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True

Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True

Case 3
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Case 4
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Case 5
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Case 6
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True

Case 7
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True

Case 8
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True



Case 9
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True

Case 10
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True
Col = Col + 2
Alpha = NumToAlpha(Col)
ExSheet.Range(Alpha & Col).EntireColumn.Hidden = True


End Select



Alpha = NumToAlpha(EndCol)

ExSheet.Range("B1:" & Alpha & TotalLine).HorizontalAlignment = xlCenter

End Sub

coachdan32

http://www.malehsbaseball.com

 

[PHV]

And this ?
ExSheet.Range(Alpha & "1").EntireColumn.ColumnWidth = 10
Are you sure NumToAlpha always returns valid letter(s) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244