Click here to Skip to main content
15,883,606 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
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

Hey there,

Managed to do it through this link:

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

Tks for the help

Best regards
 
Share this answer
 
Comments
Patrice T 28-Mar-16 13:45pm    
Use Accept answer to close the question.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900