• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

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 !
0
UserName935
Asked:
UserName935
  • 3
  • 3
4 Solutions
 
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 CoachmanCommented:
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
Jeffrey CoachmanCommented:
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 CoachmanCommented:
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now