VBA, Access, Excel - Changing lines of code in Excel from Access

Good Day Experts,

I have a 99 worksheet excel spreadsheet that has been handed to me.  This workbook has several macro's in it that need to be run on a monthly basis.  Prior to the macro, in the excel workbook, being run each month, there are 2 lines of code that need to be updated.
One line that needs to be updated, is a line that gives the number of last month, so, if it is August, I would need to change the number in the macro to june (6).

The other line is formatted like this.  If last month had (4+8), it would need to be changed to (5+7).

so, the basic question is:

Can a person change some lines of code housed in an excel spreadsheet from an access database?

Thanks !
UserName935Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

CPearson has a tutorial concerning the programming of the VBE

http://www.cpearson.com/excel/vbe.aspx

you can by Creating the Xlobject in Access control the workbook code

in particular see chapter
Searching For Text In A Module

Regards
0
Jeffrey CoachmanMIS LiasonCommented:
Can a person change some lines of code housed in an excel spreadsheet from an access database?
If I am understanding our question correctly...
   Open Access vba and "Navigate" to an Excel module, ...then edit that Excel code...
...The answer is no.

Although VBA is consistent across all office versions, ...each is different.

In Access vba you cannot navigate to an Excel module directly, ...you cannot even set a reference to another Office app (afaik)

You can certainly export the Excel VBA code out to a text file, ...then import that into the Access VBE, ...
But this is problematic because Access will not have access to the Excel events: WorkBookOpen, WorkSheetSelectionChange

Am I misunderstanding?Why not edit the code in Excel?

JeffCoachman
0
UserName935Author Commented:
Thanks to both !

I will look up that chapter and see if helps.  I can deal with the import of the macro, changing it and then putting it back.

The reason I wish to do this from access vba is because there are about 10 different sources of data that need to be gather prior to the processing of the data before it goes into the spreadsheet.  and quite frankly, I hate spreadsheets.  Especially when people try and use it to house data that should obviously be in a database.  ie: treating excel as a make shift database.

So, in that light, I wanted to complete as much of the data manipulation from access.  Once, I would be able to complete all of the pre-processing from access, and, get the bosses buy in via automation, then I would move it to a vb.net type platform.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
Again, ...you can import the raw Excel code to Access and edit in Access, ...then export it back to Excel.
But why bother?

The reason I wish to do this from access vba is because there are about 10 different sources of data that need to be gather prior to the processing of the data before it goes into the spreadsheet.
But again, why bother?
Editing the Excel VBA in Access offers no advantages (regarding the other sources of data), ...and may even create more problems than it solves...

JeffCoachman
0
UserName935Author Commented:
Would I be able to import the excel code into access then run the code from access against the excel workbook?
0
Jeffrey CoachmanMIS LiasonCommented:
The code would have to be modified to run in MS Access and "reference" the workbook in question.
Possibly opening it and performing all of the needed modifications, ...then closing it.

In your Access VBA editor, set a reference to the Excel Object Library:
Tools-->References:
excel reference
Here is code (that runs in Access) that opens an Excel file, ...then shows you with the value in cell B21 on sheet2, , ...then prompts you for what you want to change it to.
Then saves and closes the workbook:

'Declare the variables
Dim lngVal As Long
Dim xlTmp As Excel.Application
Set xlTmp = New Excel.Application

'Open the workbok
xlTmp.Workbooks.Open "C:\YourFolder\YourExcelFile.xls"

'Do not make the workbok visible
xlTmp.Visible = False

'Set the variable equal to the sheet/cell value
lngVal = xlTmp.Sheets("Sheet2").Range("b2")

'Change the sheet/cell value to the value the user types into the inputbox.
xlTmp.Sheets("Sheet2").Range("b2") = InputBox("The value is: " & lngVal & ", Change to:")

'Save and close the workbok
xlTmp.ActiveWorkbook.Close SaveChanges:=True
'Quit Excel
xlTmp.Quit
'Set the the Excel variable to nothing
Set xlTmp = Nothing

Open in new window


JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
UserName935Author Commented:
I have tinkered with this issue and have decided to migrate everything to a SQL/VB.Net solution, and, all of your comments had helped me come to this conclusion.  Thank you for the input....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.