none
Calling a Date Picker and File Explorer from a VB Module Inside an Excel File RRS feed

  • Question

  • Good afternoon, all -

    I have a .csv file that is the dump from a query on a third party site. This file is imported to an Excel template using Data|From Text/CSV.

    Once I've manipulated the data as necessary - mostly the removal from 72 columns all but the 21 I need to retain - I want to save the current sheet as a .csv over the original .csv file. In this way, the remaining file will be much smaller than it was.

    Unfortunately, if I'm understanding this properly, the SaveToDirectory value of the current sheet would be the directory of the original template file, not the directory that contains the beginning .csv. And, that directory is dynamic; i.e., it changes monthly and I'm not sanguine about modifying the code every month to account for the change. But, if I can get a file-picker to pull up - a file explorer window, if possible - from within the VBA module of the template, then I can pick the folder and assign it to a variable.

    Also, the file name is a construction built around the beginning and ending days of the report. So, I'd also need a date-picker to grab those two dates and use them in the name.

    But, these things are form items - to the best of my knowledge - and I don't know how to get them to call up from a VBA module in Excel. And, I do not have access to VBasic to create forms, anyway.

    So, my ask is, can anyone provide some assistance on how to browse to a folder and how to select dates from within an Excel VBA module?

    I greatly appreciate any sort of assistance, even if it's just pointing me to a resource of some sort. My online searches have struck out, so far.

    Thanx in advance for any help!

    Thursday, May 2, 2019 6:12 PM

Answers

  • Adam,
    re: custom save

    If you do a 'Save As' instead of 'Save' you get a Folder Picker presented to you.
    The folder picker also has an option to create a new folder.

    • Marked as answer by Adam Quark Monday, May 6, 2019 7:32 PM
    Thursday, May 2, 2019 9:40 PM

All replies

  • Adam,
    re: custom save

    If you do a 'Save As' instead of 'Save' you get a Folder Picker presented to you.
    The folder picker also has an option to create a new folder.

    • Marked as answer by Adam Quark Monday, May 6, 2019 7:32 PM
    Thursday, May 2, 2019 9:40 PM
  • Adam,
    re: custom save

    If you do a 'Save As' instead of 'Save' you get a Folder Picker presented to you.
    The folder picker also has an option to create a new folder.

    Hmm. All the search results I got had the folder defined in the code; either directly or as a coded construction. But, my search fu is not the best in the world.

    However, with a Save As type capability, that'd be just what I need in that respect.

    Thanx!

    Friday, May 3, 2019 12:29 PM