Automate copying data from one Excel sheet to another

[taklong]

Hi, forgive me for being completely oblivious.

I just got Macro Scheduler and I have no clue about scripting nor how the functions work. Watching the results that many users have, I'd really like to understand how to do the same with my 2 excel sheets. The process is as follows:

1) I send out every wednesday google doc requests to my customers asking them to complete their requests of vegetables they'd like (their order basically)

2) I need to retrieve/download this google doc data (customers orders) stored in an excel sheet manually from the internet (Wednesday - Saturday) and then manually continuously update that new information/data in to another excel sheet .

3) By saturday night I need to use the accumulated data I have, to compile a final packing list.

4) A side note, depending on each customers order, there are various inputs that needs to be made: for example, if a customer order is "big box" they will get 900 grams of a specific vegetable and 450 grams of a different vegetable etc. so the variables depend on different contingencies/factors like Max 7500 grams and Minimum 4500 grams for big boxes.

As you can see this is very tedious, I'd like to automate this process but have no clue on how to. Would very much appreciate any help I can get.

I know that there is an example of importing excel to notepad, and work my way around that. However, I am still struggling with understanding that example !

Thank you !

[Marcus Tettmar]

Hi,

There may be a number of ways to achieve this. To be honest it's going to be hard to give you exactly what you need here in one answer and if you are completely new to any kind of programming you are likely to find it quite technical. It might be best for us to arrange a desktop sharing session when we can run through it, look at what you do, give you some advice and get you started.

If the data is in Google Docs I would consider using the API rather than manipulating the interface. But this might be too technical for you.

Otherwise we would need to manipulate the web browser / send keystrokes to it / use WebRecorder/IE automation functions, to download the spreadsheet to Excel. Once in Excel we can use the XL functions to loop through the data - as you say there is a sample that shows you the basics of that.

I'm hazy on what you want to do next and I can't really tell you exactly what to write.

Please feel free to contact support and we'll set up a time when we can look more closely at your requirements and try and get you started.

Alternatively if you break the process down into more distinct parts it might be easier to help you on the forums.

[taklong]

Hi Marcus,

Wow, I really appreciate the prompt reply.

So step by step in more distinct and detailed workflow is as follows;

1) Log-in to Gmail, once logged in, go to "Drive" and amongst all the other files ... download the Google Doc named "Pick your own veggies for December 12" as an EXCEL FILE.

2) Open the downloaded Excel File .... and according to specifications or data within ... if there is a request like for example customer request for Broccoli, we would then copy that confirmation in to ANOTHER excel workbook called "Packing list" where the customer name .... order details and etc. is accumulated.

So for every value/request from customer (from the downloaded request in Google Doc), we would want to update the "packing list" excel workbook with that request.

And we would want this process to be in a loop (since Google docs continuously receive new order requests every 30 minutes) and update the "Packing List" Excel sheet with this.

Thank you very much for the help, I'd love to provide you screenshots of the process if that would ease the interpretation. Once again, very much appreciate the help and input on this, thank you.

[Marcus Tettmar]

So let's deal with 1 first. We need to find an easy way of downloading a google spreadsheet to an XLSX file.

We could click on links, invoke html elements, simulate mouse and/or keyboard events etc ... but surely there's a shortcut.

There is. It's documented somewhere within the Google Drive API but it is also possible to figure it out by watching the HTTP requests when you download to XLSX.

Go to your spreadsheet in your browser. You'll see a URL something like this:

https://docs.google.com/a/mjtnet.com/sp ... 0M3c#gid=0

Note the Key - the bit after Key= and before #. In this case it is:

0AlK_0dgXn0T4dDdNdldCbGt2ek53blJXMFhkVnY0M3c

And in your case you'll see something other than mjtnet.com

Now change the URL to this and watch what happens:

https://docs.google.com/a/mjtnet.com/fe ... 3c&fmcmd=4

Note I have changed the /spreadsheet/ccc? part to /feeds/download/spreadsheets/Export? and I have removed the #gid=0 part and replaced with &fmcmd=4

Essentially you just use the same format as above replacing mjtnet.com with your account name and the key with your spreadsheet key.

Press enter and you'll see this instantly downloads a .XLSX file.

Brilliant, we now have the direct URL to cause the Excel file to download.

Now, if we assume that your browser is already running and logged into Google Drive we won't need to automate logging in and ALL we need to do is have Macro Scheduler open that URL and bingo the Excel file is downloaded.

Note that the file downloaded has the same name as your spreadsheet but with the .xlsx extension.

So, the script below does precisely this using Chrome. I've used Chrome because it doesn't ask for permission when downloading files. It just gets on with it and downloads. If using IE you'd need to either automate clicking Save on the file download box, or install WebRecorder and use WebRecorder's OnDownload option to use it's automatic download manager.

Here's the script that uses Chrome:

Code: Select all

Let>chrome_path=C:\Users\User\AppData\Local\Google\Chrome\Application\chrome.exe
Let>download_dir=C:\Users\User\Downloads

DeleteFile>%download_dir%\test.xlsx

Run>"%chrome_path%" https://docs.google.com/a/mjtnet.com/feeds/download/spreadsheets/Export?key=0AlK_0dgXn0T4dDdNdldCbGt2ek53blJXMFhkVnY0M3c&fmcmd=4

Label>WaitForFile
IfFileExists>%download_dir%\test.xlsx
Else
  Wait>1
  Goto>WaitForFile
Endif

XLOpen>%download_dir%\test.xlsx,1,xlH

It sets up some variables to specify where Chrome lives and where the download folder is. It then deletes any files already in the download folder that we want to download so that we can be sure it will be named correctly. We then run chrome, passing our special URL to it. The next part just waits for the file to exist. And finally it opens it in Excel.

Remember the file will be named after the name of your spreadsheet. In my case it was Test. In your case it is something different, so change it. And don't forget to change the URL to YOUR url for YOUR google drive accounts (it won't be mjtnet.com) and spreadsheet key.

You might want to add some code to close the new tab in chrome afterwards. CTRL-W closes the current tab, so you could simply add this before your XLOpen line after the file is downloaded. This avoids you ending up with lots of open tabs.

Press CTRL
Send>w
Release CTRL

You can now schedule this macro to happen every few minutes, or make it loop, or whatever.

So that's step 1. For step 2 it would probably make more sense to start a new topic as you now have an Excel file and any further questions are regarding automating Excel. Lots of examples on the site on how to automate Excel.

[taklong]

Hey Marcus,

Oh my goodness, you are a GENIUS, this helped me a lot, thank you so much !

Best