Some websites, especially financial websites, have a link which allows you to download an Excel file into the Download folder. The file can then be opened and used as any normal Excel file.
Is there some way for Pan6 to capture the data in the Excel file during the download, and place it in a variable? The loadurl command does not do this for an Excel file download.
Alternatively, can Pan6 direct where to place the downloaded Excel file, i.e., specify a different folder to place it in?
This may be too extreme for Pan6 to handle. Any help will be appreciated.
Look for an option to export the data as a CSV file instead of as an Excel file. Panorama can read CSV files.
Panorama doesn’t have any way to influence where your browser places a downloaded file. You may want to look into an app called Hazel, which can automatically reposition files based on rules you set up.
This is true, but unlikely to be helpful for @vicelly. First of all, he would have to get the URL somehow and place it in Panorama code. That’s probably way more trouble than just downloading and moving the file. But there’s a good chance that there is no URL, but instead that there is some JavaScript involved in setting up the download. If that’s the case, SaveURL cannot be used. Also, I’m sure his financial web site requires him to log in to allow him to get to the download function. In old versions of macOS Safari would share those credentials with Panorama, but in more recent versions the credentials are not shared. So Panorama would not be logged in and would not be able to download the file. (And even if it could, it would not be able to understand the data if it is in Excel format.)
Bottom line, look for a CSV export option from the web site.
Yes, Jim. No doubt the download itself must be triggered by some fancy internal coding in the website. In the past, some websites (e.g., Yahoo Finance) had an Excel download capability which could be automated in a procedure. I was able to figure out how. But Yahoo Finance changed that, and it’s no longer possible. And it’s definitely not possible in the website I am using.
I have a procedure which “prepares” a file, and another procedure which manipulates that file after importing the downloaded data. The import consisted of downloading the file, and then saving it as a tab-delimited text file. From that point on, Pan6 was able to proceed with no problem.
I was hoping to automate the entire process, but I guess that’s not going to be possible. So I suppose I will continue to need two separate macros, which sandwich the manual Download-Save. Not ideal, but not terrible.
Vic, what app(s) are you using to “prepare” and “manipulate” the Excel file? I’ve never worked with downloading Excel files but I have written Panorama code to open an Excel file, save it as a text file, open the text file and import the data, if that’s any help. It utilises a separate Excel file containing a simple Auto_Open Excel macro. Creating such a macro is a simple task.
If this might be of assistance, contact me via the Message facility (click on my icon and click on the Message button).
Perhaps my original question was not worded as carefully as it should have been.
I have a Pan6 file, with some pre-existing data in it. There are two macros in the file.
Macro 1 within the file prepares the data - basically some sorting, grouping, and coloring of records. There are some calculations also. After that macro ends, from my browser I manually download an Excel file from a website, and save that file as a tab-delimited text file.
Then Macro 2 is actuated, which imports the data from the text file, and “manipulates” the data in a certain way. It is easy to distinguish newly imported data from pre-existing data because the previous data records are colored. Certain records in the combined file are deleted, and the remaining records become the “pre-existing” records for the next time the macros are run.
My objective was to automate the entire process, so that running one macro would “prepare”, download, save as .txt, and “manipulate” final records. Many years of Pan6 spoiled me into thinking the program could do everything except brew morning coffee. What a bummer that it can’t to this!
You could, of course, get by with just one procedure (which you call a macro). You could download the Excel file first and then have a single procedure prepare the data, open the Excel file, save it as a text file, import that file and process the data. But if what you have works, stay with it.
Vic, what app(s) are you using to “prepare” and “manipulate” the Excel file?
Hello Michael,
Perhaps my original question was not worded as carefully as it should have been.
I have a Pan6 file, with some pre-existing data in it. There are two macros in the file.
Macro 1 within the file prepares the data - basically some sorting, grouping, and coloring of records. There are some calculations also. After that macro ends, from my browser I manually download an Excel file from a website, and save that file as a tab-delimited text file.
Then Macro 2 is actuated, which imports the data from the text file, and “manipulates” the data in a certain way. It is easy to distinguish newly imported data from pre-existing data because the previous data records are colored. Certain records in the combined file are deleted, and the remaining records become the “pre-existing” records for the next time the macros are run.
My objective was to automate the entire process, so that running one macro would “prepare”, download, save as .txt, and “manipulate” final records. Many years of Pan6 spoiled me into thinking the program could do everything except brew morning coffee. What a bummer that it can’t to this!
Yes, by downloading the Excel file first, one procedure will do everything. When I first wrote the procedures, I always checked the results of the “preparation” procedure, before continuing with the download and Macro 2. I got into the habit of that chronology, and never broke it. But now that I’m confidant of Macro 1 results, I could combine the two procedures. Better yet, I can have Macro 1 “Call” Macro 2 just before it ends.
I noticed your name from earlier posts, and I see you are a regular contributor to the forum. Thank you for responding to my inquiry.
| 
michael: