By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,057 Members | 1,401 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,057 IT Pros & Developers. It's quick & easy.

RTE 1004 when using Access to create/populate Excel

P: 1
Hi All

I am currently using Access to output to excel. The creation of the worksheet goes ok, and then I have a piece of code (recorded from an Excel macro) to create pick lists for two columns in my newly created excel file.

This is supposed to run on a loop to create multiple spreadsheets, however, after successfully creating the first sheet I get an error and the process fals over.

This is the error I get from the debugger:

RTE 1004:

Method 'Sheets' of object '_Global' failed

This is the offending code:


Sheets("02").Select
Columns("F:G").Select
Selection.Interior.ColorIndex = 43
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
Columns("F:F").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$T$1:$T$6"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
ActiveWindow.LargeScroll ToRight:=-2
Columns("G:G").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$U$1:$U$36"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
ActiveWindow.LargeScroll ToRight:=-2

Any help would be greatly appreciated as I'm pulling out whats left of my hair!!!

Thanks in anticipation.....Steve
Nov 24 '06 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.