I need to access Excel "Save As" programmatically (I am not a programmer)
Hello Excel forum and thank you for being here !
I receive weekly 10-20 CSV files that range from 20 to 200 MB in size (network scans)
My problem is that device creating the files I receive do not load into my ETL software (iDashboards)
What I have had to do is load each CSV file into Excel (2019 in this example) and perform
File-->Save As --> CSV (MS-DOS)
The output file loads without issue into our ETL software.
(I noticed that the process does add commas and change the file structure somewhat, so I know the data inside is being manipulated )
What I am trying to find is a way to programmatically accomplish this manual "Save As" process in Excel
How could this be done?
How can this function of excel be accessed from a command line? PowerShell? or some other programmatic fashion?
Thank you for any insight and help that can be provided
Last edited by AliGW; 10-22-2021 at 08:58 AM.
Reason: Irrelevant section of title removed.
Re: I need to access Excel "Save As" programmatically (I am not a programmer)
Hi and welcome
You could build a tool in Excel to do all this for you.
The CSV files could be saved into a specific folder and at the click of a button a VBA script run that opens the files one by one and saves them to the desired output format in a different folder.
You can then upload them to iDashboards without issue.
If that sounds like it would work for you, attach a sample workbook or two (desensitized) and we can help with the code.
3) Save that .xlsm file to the folder you created in step 1 and call it "AutoRunFile.xlsm"
4) Also inside that folder create two sub folders called Before and After
5) Save your pre-processed CSV file in the Before folder
6) Open Notepad and put the code below in a new document.
7) Save the Notepad file.
8) Navigate to the folder you saved the Notepad file to and change the file extension from .txt to .vbs
You can now add a task to Task Manager to open the .vbs file and it will loop through the files in the Before folder, open them in Excel and save them to the After folder as .csv MSDos filetypes.
Plenty of info on how to add tasks can be found online so I won't go into that here.
I open the file up before anything else to check and enable macros
So my folder structure looks like
e:\AA
contains 4 items:
-Script.VBS
-AutoRunFile.xlsm
-After(Folder)
-Before(Folder)
When I schedule the task in task scheduler (create basic task, no arguments or start in)
I open properties and change it from "server 2008" to "windows 10" and Configure for and select "Run with highest privileges "
and then run it manually the following happens
Excel opens up with a window saying
"A file names E:\AA\AutoRunFile.xlsm already exists in this location. Do you want to replace it?
(Yes No Cancel) - No is heighlighted.
I chose "No" then---
I get Run-Time error '1004'
Cannot access 'AutoRunFile.xlsm'
(End Debug) Continued is greyed out.
I hit end and I get a windows script host error popup window:
Script E:\AA\Script.vbs
Line 6 Char 1
Error unknown runtime error
Code 800a9c68
Source: MS VB Script runtime error.
How did I cause so much chaos????
Last edited by AliGW; 10-26-2021 at 10:50 AM.
Reason: Code tags added.
Re: I need to access Excel "Save As" programmatically (I am not a programmer)
I don't think you've caused the chaos at all, as it's my fault!
The code I posted previously was a copy of code I'd first written rather than the final debugged/fixed code. Not sure how that happened but apologies for the confusion.
The error message showed up because the original code was trying to save the file into the same folder as the file was opened from.
Try the below version which has a source and target folder defined. This is the final code I wrote and that worked, but now tweaked to include your path.
MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
Posts
62,101
Re: I need to access Excel "Save As" programmatically (I am not a programmer)
@runatyr
Administrative Note:
You need to include code tags around your code.
Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.
Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
(Note: this change is not optional. As you are still new here, I will do it for you THIS TIME.)
For future reference, there are instructions at the top of the page explaining how to attach your sample workbook.
Ali Enthusiastic self-taught user of MS Excel who's always learning! Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.
Forum Rules (updated September 2018): please read them here.
How to use the Power Query code you've been given: help here. More about the Power suite here.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks