Input & Output files

tombrown65

New Member
Joined
Jun 9, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hi Guys,

I need a quick primer that I can work from; once I have the basics I can normally build on it to get to the end

I have a master template in excel that I have put some formulae & macros in so that when I copy a csv file into one tab another tab collates all the data into a summary table, including sorting it alphabetically and removing some spurious entries. At the moment I save the master template first as a new filename, then copy the csv content in, then run the macros.

I would like to have a macro that displays a dialog box that prompts for a new filename to save the template to. If that was all I wanted to do I could just use Application.GetSaveAsFilename. However I want the same Dialog box to have a file picker field for the csv input file - i.e. in one dialog I pick both the input & output filenames, and store them as variable, so I can then save as one and open the other to copy data across.

As an added bonus if there is an easy way of copying the content of a csv into a tab in the excel workbook (other than select copy & paste) that would be great.

Apologies if this is answered elsewhere - I did a quick search, but couldnt find anything obvious

thanks
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
Welcome to the board.

I'd use Power Query with VBA to solve this

Set up a table with two columns for input file name and export csv name.
If tables are new to you, look up "Excel Insert Table"

Don't over complicate it with wanting a single dialog box, start by using a single dialog box for each required value (I assume this is for the benefit of the end-user).

Once you have everything else working and tested, you can revisit this if you really have to, but it seems like getting the correct flow of input to output data is more important than how these filenames are created (i.e. what creates more end value or task completion?)

After this, you can use Power Query to pull the data from the input file, do any data transformations (e.g. check data types, create relationships, add columns with formula or conditions etc) and then connect the output to another sheet or write directly to the named CSV variable.
If Power Query is new to you, look up "What is Power Query" or search for "Power Query pull data from CSV file".

I find PQ is more stable than VBA and needs less attention to detail (e.g. last row value), whilst using tables simplifies data flow processes.

However, I still use VBA for User events, e.g. double-click, worksheet change, refresh data via PQ within a button, cell formatting etc
 

tombrown65

New Member
Joined
Jun 9, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Welcome to the board.

I'd use Power Query with VBA to solve this

Set up a table with two columns for input file name and export csv name.
If tables are new to you, look up "Excel Insert Table"

Don't over complicate it with wanting a single dialog box, start by using a single dialog box for each required value (I assume this is for the benefit of the end-user).

Once you have everything else working and tested, you can revisit this if you really have to, but it seems like getting the correct flow of input to output data is more important than how these filenames are created (i.e. what creates more end value or task completion?)

After this, you can use Power Query to pull the data from the input file, do any data transformations (e.g. check data types, create relationships, add columns with formula or conditions etc) and then connect the output to another sheet or write directly to the named CSV variable.
If Power Query is new to you, look up "What is Power Query" or search for "Power Query pull data from CSV file".

I find PQ is more stable than VBA and needs less attention to detail (e.g. last row value), whilst using tables simplifies data flow processes.

However, I still use VBA for User events, e.g. double-click, worksheet change, refresh data via PQ within a button, cell formatting etc
Thanks for the direction. Are you saying I need to populate a table with filenames and the dialog queries them or that the dialog populates them?

I agree on separating out the dialogs, was thinking that way, but wanted to avoid the lazy approach of using Application.GetSaveAsFilename, as that woudl limt my ability to combine at a later date
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
I'm suggesting:
Create a table that Power Query connects to (which contains the relevant full file paths and name)
Extract data from the source file via PQ only (not VBA)
Do any data cartwheels and manipulations (PQ only)
Create output file (PQ only)
Then come back to implementing VBA for file dialog reasons

Did you search for Excel tables or power query and watch any videos or explainers?
 
Last edited:

tombrown65

New Member
Joined
Jun 9, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I'm saying you need to create a table, that Power Query can connect to and extract the needed file names to then read data from or write data to.

Did you search for Excel tables or power query and watch any videos or explainers? It's pretty difficult to explain without pictures, sound or animation, vs original ask for a quick primer...
Thanks for the clarification. Haven't had a chance to research more yet. Had finished work when I got your reply, will look into it today
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
No worries, try to get an overall feel for what PQ can do and then we can adapt it to your situation :)
 

tombrown65

New Member
Joined
Jun 9, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

No worries, try to get an overall feel for what PQ can do and then we can adapt it to your situation :)
I just watched one video of someone trying to sell me a course about it :), but it did, at least, give me an overview of what can be achieved. Found this, which looks good, but havent dived into it deeply yet (too many meetings) - The Complete Guide to Power Query | How To Excel
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
Good man :) I thought to post a suggestive link but sometimes it's better to discover by yourself and then ask curiosity questions

MVP Leila Gharani has a good video here
 

tombrown65

New Member
Joined
Jun 9, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Just to let you know, I have been reading up on PQ - I never knew about it before and it indeed looks to be really powerful. As I am mostly done on my current project, using VB & formulae, I'm going to complete it with VB to at least get it working. I will then take some time to go back and see if I can redo it in PQ, and hopefully never look back again

Many thanks for the pointer
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
You're welcome, I'm doing a similar exercise now to a previous project which is helping my own learning, effectively creating a version 2.0 aka new and improved lol :)
 

Forum statistics

Threads
1,140,925
Messages
5,703,208
Members
421,282
Latest member
hogie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top