Formatting exported query in excel using ACCESS - Error

[BlueWolverine]

Hello,
MS ACCESS/EXCEL 2003 on XP PRO.

I am writing code in ACCESS to format an excel document exported from the
access file. I have an error.

v_look_for = "3 (i.e. all week-end)"
v_replace_with = 3
.Selection.Replace What:=v_look_for, Replacement:=v_replace_with, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

This returns an error
<<<Run-time error '9':
subscript out of range>>>

It's not a TYPE issue because I have tried to make v_replace_with="3" as
well and it doesn't work. The range in excel that I have selected DEFINITELY
has exactly v_look_for in it. The rest of my code for embedding excel vba in
access is working. and here's my setup.

Set obj_excel = CreateObject("Excel.Application")
obj_excel.Visible = True
obj_excel.DisplayAlerts = False

Dim str_target As String, row_count As Long, str_target_col As String,
lcv As Long

With obj_excel

'do all sorts of excel stuff

end with


Any idea what's dying? Excel help says it's an array problem, but there's
no dimmed array to kill.

Thanks!

 

[ryguy7272]

So let me get this straight. You are controlling excel from Access, right.
Use this macro (in Access):

Option Compare Database



Option Explicit ' Use this to make sure your variables are defined



' One way to be able to use these objects throughout the Module is to
Declare them here, and not in a Sub



Private objExcel As Excel.Application

Private xlWB As Excel.Workbook

Private xlWS As Excel.Worksheet





Sub Rep()



Dim strFile As String



strFile = "C:\Ryan\Crosstab Query.xls"



‘Of course, this is just an example; put the actual path to your actual file
here...

' Opens Excel and makes it Visible



Set objExcel = New Excel.Application



objExcel.Visible = True



' Opens up a Workbook



Set xlWB = objExcel.Workbooks.Open(strFile)







' Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.



Set xlWS = xlWB.ActiveSheet



' Set xlWS = xlWB("Sheet1")





With xlWS ' You are now working with the Named file and the named worksheet

' Your Excel code begins here...do the formatting here...format the exported
query with Excel VBA code right here!



End With



' Close and Cleanup



xlWB.SaveAs xlSaveFile

xlWB.Close



xlapp.Quit

Set xlapp = Nothing



End Sub

HTH,
Ryan---

 

[David H]

A subscript out of range error usually indicates that the code attempted to
reference an invalid element of an array. For example, the array contains 3
elements and you attempted to reference the 4th.

Is this the entire code? I didn't see anything that would throw the error?

 

[BlueWolverine]

I found the answer to my problem!

For common knowledge.

When you write code in an ACCESS vba editor for code for an access
application, the compiler has no clue what "xlCenter" or "xlNone" means.

so in my code:

the ACCESS VBA Compiler has no clue what xlPart means. so it craps out.


2 ways to deal with this.

1) Find out the integer values of the xl constants. put the integer values
into the code in place of xlCenter etc.

or more elegantly,

2) Past code like the following into the top of a module manipulating excel,
so that you can leave the readability of the xl constants in place.
I am including a lot of them to make it simpler, and so you don't have to
look them up.

Public Const xlNone As Long = -4142
Public Const xlCenter As Long = -4108
Public Const xlContinuous As Long = 1
Public Const xlAutomatic As Long = -4105
Public Const xlEdgeLeft As Long = 7
Public Const xlEdgeTop As Long = 8
Public Const xlEdgeBottom As Long = 9
Public Const xlEdgeRight As Long = 10
Public Const xlInsideVertical As Long = 11
Public Const xlInsideHorizontal As Long = 12
Public Const xlThin As Long = 2
Public Const xlThick As Long = 4
Public Const xlLeft As Long = -4131
Public Const xlRight As Long = -4152
Public Const xlTop As Long = -4160
Public Const xlBottom As Long = -4107
Public Const xlLandscape As Long = 2
Public Const xlPaper11x17 As Long = 17
Public Const xlPaperLetter As Long = 1
Public Const xlPaperLegal As Long = 5
Public Const xlDownThenOver As Long = 1
Public Const xlPortrait As Long = 1
Public Const xlToLeft As Long = -4159
Public Const xlToRight As Long = -4161
Public Const xlDown As Long = -4121
Public Const xlUp As Long = -4162
Public Const xlPart As Long = 2
Public Const xlbyRows As Long = 1