Skip to main content

Making macro universal

Hey IS Forum!
Right now I'm trying to solve an issue I faced while using one of my macros that I made a while ago.
Purposue of my macro is perform some tasks regarding appending new columns in files that have to work with on a daily basis.
 
Lately, unfortunately, instead of one file there are several files that I receive for analysis event though it is the same in terms of its content.
I know that I can append them and run the macro but there is some specific issue that i think when resolve on my end would be able to help in my future work.
 
To the point. Is it possible, knowing that contents of given file are the same as in previous one on which a macro was created, to change somehow below listed line of code to perform  task on active (opened) database instead of specific one whith a name as in this example "DATABASE NAME"? What I mean, is possible to run my macro on different files with different names without a need to change their (databases) names or changing the code of the macro for each specific file?
 
Set db = Client.OpenDatabase("DATABASE NAME")
Thank you in advance!
 
Kind regards,
Maciej

Brian Element Mon, 06/14/2021 - 08:46

Hi Maciek,

If you want to use your script on an open/active database then you can use the following code to get the name of the file"

filename = Client.CurrentDatabase.Name

Then you use the filename variable in your code instead of the hardcoded database name.  If there is no database open this will return an error which you can capture with the on error resume next line.

The big problem with this is you have to assume that all the fields are the same name, if that is true then your script is good if it is not you have to make some other decisions such as creating some way for the user to tag the fieldnames to use in the script.  Without know more about these files it is hard for me to give you some good advice.  Are the files from the same source or different sources, if they are from the same source then the fieldnames are probably the same, if they are from different sources we probably can't rely on that being true.

Maciek Mon, 06/14/2021 - 10:16

In reply to by Brian Element

Hi Brian,
This worked like a charm for my issue :) As You describe, the fields and its contents in each file are the same thus this kind of code change workded out great for my case.
Thank you very much for a quick response with working solution :)
 
Kind regards,
Maciej