473,414 Members | 2,030 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,414 software developers and data experts.

Controlling Excel with Access VBA

I am trying to open an MS Excel file and perform a find/replace function, all using VBA within MS Access. I keep getting a "Subscript out of range." error. Do you know why I am getting this error? The code I am using is below:
Expand|Select|Wrap|Line Numbers
  1.     wrkBookName = Application.CurrentProject.Path & "\Delphi Data.xls"
  2.     Set XL = CreateObject("Excel.Application")
  3.     XL.Visible = True
  4.     Set WB = XL.Workbooks.Open(wrkBookName)
  5.     Set ws = WB.Worksheets(1)
  6.  
  7.     XL.Range("A1:Z38").Replace What:="Invoice", Replacement:="x", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  8.  
  9.  
Nov 5 '08 #1
1 1600
What line causes the error?
What version of Excel are you using?
'--
In the meantime, replace the xlConstants with their numeric value. Access does not know what they are.
Nov 5 '08 #2

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

Similar topics

3
by: Lynn A. | last post by:
I have to perform an import from an excel file to Access. The file comes originally as a csv file. I have to delete some rows and columns, then change the formatting of some columns. I am using...
0
by: Jack | last post by:
Windows 2K Pro Access/Excel 2003 Hi there, I have a large number of password protected Excel Workbooks. The files are protected by code that runs when the workbook opens. The code asks the...
1
by: Aiysha | last post by:
Hi, I want to take cotrol of notepad from excel (through VB programming). I have .prn files which have to opened in notepad and then I have to save these .prn files in .sim , all files format. ...
8
by: Charley Kyd | last post by:
I'd like to learn how to use VS.NET as though it were Excel VBA, with the goal of creating a compiled Excel addin. That is, I'd like... ....all forms to belong to the Excel environment. ....the...
0
by: Al Christians | last post by:
I'm starting to test a python application that creates an Excel workbook, then fills in values for some cells and formulas for other cells. The formulas involve circular references, which will...
8
by: GaryDean | last post by:
We have been noticing that questions on vs.2005/2.0 don't appear to get much in answers so I'm reposting some questions posted by some of the programmers here in our organization that never got...
3
by: Tim Marsden | last post by:
Hi, I am currently creating an instance of Excel using VB.NET Automation. dim xl as Excel.Application xl = new Excel.Application However, how can I have more control over the starting of...
1
Walt in Decatur
by: Walt in Decatur | last post by:
Is there a way to control Access charts to the degree that can be done in Excel? Specifically, I would like to be able to define colors and use the DLookUp function to define the input for the axis...
16
by: Phil Stanton | last post by:
I have a form with a button which is supposed to open an Excel file (With lots of Macros /VBA) in it. The Excel file gets it's data from the Access program Here is the code Private Sub...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.