+ Reply to Thread
Results 1 to 11 of 11

I need to access Excel "Save As" programmatically (I am not a programmer)

  1. #1
    Registered User
    Join Date
    10-22-2021
    Location
    Ohio
    MS-Off Ver
    2019
    Posts
    5

    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.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,693

    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.

    BSB

  3. #3
    Registered User
    Join Date
    10-22-2021
    Location
    Ohio
    MS-Off Ver
    2019
    Posts
    5

    Re: I need to access Excel "Save As" programmatically (I am not a programmer)

    Would I be able to run this in task scheduler?

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,693

    Re: I need to access Excel "Save As" programmatically (I am not a programmer)

    You should be able to, with a little tinkering around.

    1) Create a folder somewhere that this process will live
    2) Create a .xlsm file and add the code below to a standard module called Module1
    Please Login or Register  to view this content.
    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.
    Please Login or Register  to view this content.
    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.

    Hope that helps.

    BSB

  5. #5
    Registered User
    Join Date
    10-22-2021
    Location
    Ohio
    MS-Off Ver
    2019
    Posts
    5

    Re: I need to access Excel "Save As" programmatically (I am not a programmer)

    This look ideal but i appear to have gummed something up...
    I get the error below

    Attachment 752999
    I choose "No do not replace"
    Attachment 753000






    Here is my config

    Attachment 752991

    Attachment 752992

    Attachment 752993


    Any ideas what i need to adjust?

  6. #6
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,693

    Re: I need to access Excel "Save As" programmatically (I am not a programmer)

    None of your attachments worked so I'm unsure on what the issue is.

    I'm guessing it's trying to overwrite versions of the file that already exist due to the code being run twice perhaps?

    BSB

  7. #7
    Registered User
    Join Date
    10-22-2021
    Location
    Ohio
    MS-Off Ver
    2019
    Posts
    5

    Re: I need to access Excel "Save As" programmatically (I am not a programmer)

    I am so sorry those did not attach.. .well I can type it out


    My folder is named E:\AA
    Inside that I have 2 subfolders Before and After


    In module 1 I have

    Please Login or Register  to view this content.
    The XLSM named AutoRunFile.xlsm is store in E:\AA

    I have this text below as a Script.VBS
    Please Login or Register  to view this content.


    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.

  8. #8
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,693

    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.

    Please Login or Register  to view this content.
    Fingers crossed this one works!

    BSB

  9. #9
    Registered User
    Join Date
    10-22-2021
    Location
    Ohio
    MS-Off Ver
    2019
    Posts
    5

    Re: I need to access Excel "Save As" programmatically (I am not a programmer)

    By Jove that did the trick !!
    I have to say a thousand thank-you to you !

    This was a major obstacle for me!!

    Thank you again and again and again!

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    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.

  11. #11
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,693

    Re: I need to access Excel "Save As" programmatically (I am not a programmer)

    Glad I could help

    BSB

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 03-02-2020, 02:08 AM
  2. [SOLVED] Date in Excel ("30/09/2016") copies incorrectly into Access ("30/05/1905")
    By lukelucky in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-30-2017, 05:19 AM
  3. Code only runs when a user hits "Save", not "Save As"
    By cjstewart8 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-04-2014, 07:41 PM
  4. Replies: 2
    Last Post: 05-10-2014, 06:58 PM
  5. [SOLVED] Script to save to folder in "v:\dept2", confirm file saved, then delete from "v:\dept1"
    By ks100 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-28-2014, 11:42 AM
  6. Is it possible to programmatically "Save with thumbnail"?
    By Skotzmun in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-09-2014, 02:03 PM
  7. Create a Command Button to "Save As" and "Close" an Excel Workbook
    By thedunna in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-26-2013, 05:38 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.07349 seconds
  • Memory Usage 9,208KB
  • Queries Executed 15 (?)
More Information
Template Usage (34):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (5)bbcode_code
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (11)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (11)postbit_legacy
  • (11)postbit_onlinestatus
  • (11)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (49):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1