Huge undertaking, Need help, Can excel do this? (auto start macro based on time, ignore popups, "scrape files")

[USFMD82]

I am about to undertake a large scale project in my office. currently we run a daily report setup that takes almost the entire day to run. I am trying to see if I can clean up the code. Unfortunately the guy that created the code is no longer here (moved on to greener pastures). If you could let me know if the items I'm trying to do are possible and maybe tell me the functions or VBA script id look at so I can start my research on it?

• Auto Start the macro based on specific time
  • Id like to somehow make the macro start at 1AM every business day, I have seen in places this is possible
  • I assume I must have the computer turned on with excel running (possibly host the file on a SharePoint site to ignore the computer being on?)
• Ignore popup errors/requests
  • throughout the course of the macro running several popups come up that we always push "ok" or "update" is there a way to automate that?
• "scrape" a files contents (all tabs) and put it back when finished
  • So I am on a computer in an office in one city the server with the files I am accessing sit in a city 7 states away, the macros take forever largely due to the speed of the connection, maybe there is a better way but my thought process is to grab the content of the file on the other server put it locally on my computer, do all the formulas etc then put it back on the other server updated.

The dream would be to have this thing run by itself before I come in in the morning.

Sorry I am coming to you all with such a difficult question, I appreciate the help.

 

[Bob Ully]

If you can copy the files to your computer, and then run the macro's, it should run better. Where I work we use a network, and some decent sized Access Databases, 300,000 records. Going thru the network to do a large global delete on 1 database can take 30 minutes, but copying the file, and doing it locally takes only 10 minutes. It is easy to copy files, in the VBA macro language.

The pop ups are with the VBA code, they should be easy to spot. Start with these 2 items.

 

[Lplater]

The first one I believe I can help you with as it's what I have done recently myself.

Record what function you want to occur in a normal macro and for example call it Testmacro1

Once is recorded right click on the excel icon next to the file icon top left of your screen, scroll down and select view code, a module will open up.


Paste this in to the module:

Private Sub Workbook_Open()

Application.OnTime TimeValue("01:00:00"), "Testmacro1"

End Sub

When your workbook opens at 1:00 am your Testmacro1 should run.

To others that read my post, If I'm incorrect please say so.

jas

 

[Lplater]

The item your referring to as scrape, is that not possible to do this via a web query using the url of the webpage holding the data you need ???

 

[USFMD82]

The item your referring to as scrape, is that not possible to do this via a web query using the url of the webpage holding the data you need ???

Sorry, to my understanding the files are not at a webpage with a URL, they are on a network drive located in out other office so I am not sure if this is a valid idea or not.

Thank you all for the support thus far, I will be learnign how to copy a whole file and save it to my hard drive when the directory may or may not exist (unless other users run the macros). I will keep updating this thread as I have questions. Thanks again so much for the help!