Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Run-time error '1004'

Status
Not open for further replies.
coachdan

coachdan

MIS
Joined
Mar 1, 2002
Messages
269
Location
US
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

 
Sort by date Sort by votes
How about something like:
Code: 
ExSheet.Columns(Alpha & ":" & Alpha).Select
Selection.ColumnWidth = 10.00

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations
 
Upvote 0 Downvote
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

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kennelbloke
  • Locked
  • Question Question
TransferSpreadsheet 3125 Error
Replies
5
Views
2K
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question Question
Find error in currency feild
Replies
8
Views
1K
Andrzejek
Andrzejek
Aurillius
  • Locked
  • Question Question
Convert Date/Time cell to a date and time in VBA
Replies
2
Views
1K
Andrzejek
Andrzejek
Kim296
  • Locked
  • Question Question
Sending Gmail using vba Send error
Replies
6
Views
2K
strongm
strongm
EM1107
  • Locked
  • Question Question
calculated time difference.
Replies
2
Views
906
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top