Vb.net to audit events in excel VBA

Question

0.00/5 (No votes)

See more:

Hello There!

I've a Vb.net Project that it is used to schedule, launch, and run a given macro of excel workbooks.

Here's the code:

VB

If File1.Text <> "" Then
                    xlApp = New Excel.Application
                    xlworkbook = xlApp.Workbooks.Open(File1.Text, Local:=True)
                    xlApp.Visible = True
                    If mymacro1 <> "" Then
                        xlApp.Run(mymacro1)
                    End If

                    If mysave1 <> "" Then
                        fname = xlworkbook.Name
                        lpath = xlworkbook.Path & "\"
                        My.Computer.FileSystem.CopyFile(lpath & fname, mysave1 & fname, True)
                    End If
                    xlworkbook.Close()
                    End If

What I'd like to know is that if it is possible to create an audit/log for each action in vba.

Currently, i've set such log to excel worbook by using:

I'd like to know if it's possible to set the vb project to read and write to a txt/csv every action done by the excel macro.

Best Regards,

FC

What I have tried:

Currently, i've set such log to excel worbook by using:

VB

Set mytxt = CreateObject("Scripting.FileSystemObject")
Set mylog = mytxt.createtextfile("Log_" & Now)
mylog.writeline Now & " | " & 'Action'

But the intend is to create a button to enable/disable the creation of this log, without the need to code this into the excel macro.

Posted 22-Mar-16 4:02am

Fjcosta

Add a Solution

Comments

Maciej Los 22-Mar-16 11:38am

What is "action"? What do you mean? What do you want to achieve?

Fjcosta 22-Mar-16 13:00pm

Here's an example of a code and an output:

Excel vba Code:
-----------------------
Sub sample()
DIm wb as workbook
dim ws as worksheet
dim fname as string
dim fsheet as string
set wb = thisworkbook
set ws = wb.sheets(1)
Dim fdate as string

fname = wb.name
fsheet = ws.name

fdate = format(now, "DDMMYYYY")
Set mytxt = CreateObject("Scripting.FileSystemObject")
Set mylog = mytxt.createtextfile("Log_" & fdate)
mylog.writeline Now & "|" & fname & " opening"

ws.select
mylog.writeline now & "|" & fsheet & " Selected"

For each cell in range("A1:Az3")
mylog.writeline Now & "|" & cell.address & "|Evaluated"
if cell.value = "" then
cell.value = cell.address
mylog.writeline Now & "|" & Cell.address & "|Updated"
end if
Next

-------------------
Output:

Txt Name: Log_22032016

22-03-2016 15:40:33|sampleworkbook opening
22-03-2016 15:40:34|sampleworksheet selected
22-03-2016 15:14:34|a1|Evaluated
22-03-2016 15:14:34|a1|Updated
22-03-2016 15:14:34|b1|Evaluated
22-03-2016 15:14:34|b1|Updated
22-03-2016 15:14:34|c1|Evaluated
22-03-2016 15:14:34|d1|Evaluated
22-03-2016 15:14:34|d1|Updated

etc..

------

It doesn't need to be this accurate.. i just need something to control the duration events in excel.

Something like a major worksheet_change, in a bigger scale.

Maciej Los 22-Mar-16 14:03pm

Well... this sounds very strange, because i have no idea:
1) why to use Excel macro to do some stuff, if you can fully control MS Excel direct from VB.NET code;
2) why to use obsolete objects/functions (such as: CreateObject("Scripting.FileSystemObject")) to create log file,
3) why do you want to log each event?

Patrice T 23-Mar-16 1:59am

Your solution look weird.
What do you want to achieve? Why?

Fjcosta 23-Mar-16 9:02am

In my opinion, they should just use ssrs to get the job done, but excel usage is a must.
@Maciej
1) The vba macros are already built; the user doesn't want to change his method of work. He just needs to schedule the files running time and which macro to use;
2) That's just an example sent to me in order to i get the idea, since i was also confused.
3) As far as i understand, this solution will be running in a remote desktop, saving the final files in a shared cloud. If something goes wrong or takes too much time, he wants to know. Mirroring excel events to a txt is really bugging me; I sugested editing the existing macro through vb.net... "Don't get yourself with ideas" was the answer.

@ppolimorthe

sent to me:
Vb.net to schedule(date and time) at least 5 excel files, with an option to choose up to 3 macros to run via textbox (insert vba macro name); the files shall be saved as .xlsx and in a user defined path, with a log(filename_ddmmyyyy.csv) that shall be deleted after 10 days. Remove any power saving setting since the pc must stay on.

as far as i could see, the files were linked to SQL Server through odbc; i asked about if the command would change, but no, just one of the parameters linked to a cell.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Fjcosta · Answer 1 · 2016-03-28T07:31:00

Solution 1

Hey there,

Managed to do it through this link:

https://support.microsoft.com/en-us/kb/302814[^]

Tks for the help

Best regards

Posted 28-Mar-16 7:31am

Fjcosta

Updated 28-Mar-16 7:34am

Comments

Patrice T 28-Mar-16 13:45pm

Use Accept answer to close the question.