Automatically import select number of rows and columns based on user-input from .txt file using VBA

[lyrec0]

Hi,

I am developing a static mathematical model that will determine the annual energy consumption of an air handling unit based on user input.
The model calculates the energy consumption of each of the components (Fans, Pre-heating coil, Cooling coil, Re-heating coil and Humidifier) for every hour of a given year.
The model requires two main data inputs: the user input-parameters (indoor setpoints for temperature, humidity etc.) and climate-data (outside temperature, humidity etc.).

The climate data is provided as a .txt file with hourly records for each period and location, a single period spanning one decade (e.g. 2011 - 2020). These 10-year datasets contain 87600 rows of data.
There is also the current period, being 2021 - 2030, which contains 8760 rows (2021) + all of 2022's hourly records up to today (about 12000 - 13000 total at the moment).

See image 1 below for an example of the original formatting.

Image 1: example dataset

I've used the macro recorder to record me manually importing a dataset with the legacy text import-function. I've combined that code with a piece that promts the user to select a climate file, rather than having to go through the legacy import-function themselves. Then I used a counter to pull exactly 8760 rows of data from each of the aformentioned columns. See image 2:

Image 2: example of current solution

Now up to this point I haven't used VLOOKUP for this, there are IF-statements checking if the value in the 'counter'-column is between or equal to 1 and 8760, and if the statement is true the corresponding data gets pulled from the table on the left. For the temperatures the original values have to be divided by 10 to get the correct units for the model.

Above solution works for running periods, but it isn't ideal as Excel still imports thousands of unused rows (hourly data from 2022). Furthermore, I would like to be able to select a specific year from the larger 10-year datasets, for example the year 2013 from dataset 2011 - 2030.

What I came up with myself was to create an input-field in which the user can provide the first date of the climate year that has to be imported, for example: I would like to import the climate-data for the year 2013 so my input would be '01-01-2013'. Then I use VLOOKUP to pull the required data, being columns T, TD and U, from the correct year (starting at the provided start date + 8759 rows).

This solution works but it isn't feasable, I've only been able to realise this for one column (T). Excel becomes terribly slow and instable, possibly because of the size of the dataset and the number of 'VLOOKUP'-instances that need to be called (4 x 87600) to provide the user with the ability to pull a specific year from the dataset.

This got the thinking that it might be possible to use VBA to prompt the user to select a file with the Windows Explorer and be able to select which climate year has to be imported using a user input. The input can be a date or value range, e.g. '01-01-2013 - 31-12-2013', or '01-01-2013' +8759 (hours, records)', or just the hours. Any form in which the user can specifcy a specific year, and thus prevent Excel from importing the complete 10-year dataset.

Any help, insights or tips are greatly appreciated!

Thanks in advance

 

[Micron]

Selecting a file is relatively easy with the msoFileDialogFilePicker. AFAIK, it is possible to extract portions of a text file based on user input by using SearchKey or EntireLine properties (have not done so myself). You'd probably want to get the date inputs from a userform but I suspect you'll have to format dates as mm/dd/yyyy in both the inputs and the text data that you look for. Not saying you have to format the file dates as well, just something like
format(myInputDate,"mm/dd/yyyy") = format(textFileDate,"mm/dd/yyyy")

Maybe going forward you should have a file for each year rather than 10 years worth in one file? Or maybe move the data to a database.

 

[lyrec0]

Selecting a file is relatively easy with the msoFileDialogFilePicker. AFAIK, it is possible to extract portions of a text file based on user input by using SearchKey or EntireLine properties (have not done so myself). You'd probably want to get the date inputs from a userform but I suspect you'll have to format dates as mm/dd/yyyy in both the inputs and the text data that you look for. Not saying you have to format the file dates as well, just something like
format(myInputDate,"mm/dd/yyyy") = format(textFileDate,"mm/dd/yyyy")

Maybe going forward you should have a file for each year rather than 10 years worth in one file? Or maybe move the data to a database.

Thank you for your reply! The datasets are provided per decade by the source, the only dataset that doesn't contain 10-years worth of data is the current decade (so 2021 - now). I've already experimented with using Access to filter out the yearly records a bit, but I prefer staying with Excel only. I just found out that I can pull specific data from the source database through a HTTP "POST" request. The request should contain at least the following parameters: the weather-station code, start - and end date (here is where it gets interesting, I could create two variables that represent the start - and end date, which in turn are linked to a user-input form) and the variables (columns) one would like to import.

The following piece of code:

Returns (VBA Debug):

This line defines what data will be pulled from the URL, and is provided by the user:

The data I've selected in above line is imported correctly, but not into the actual worksheet yet.

I'll explain the idea that I have in short, I've got a little bit of programming experience but I barely know VBA-syntax.
I figure that if the columns, delimited by "," (comma), can be separated and assigned to a single-column array (1x8760), this single-column array can be assigned to the target column in the calculation worksheet. The input-variables above, being: stns, start, end and vars, could be assigned to user-input forms. Thus achieving the user being able to select a specific dataset, without having to filter out data from the larger datasets.

But I honestly have no idea yet on how to approach this.

Greetings

 

[Micron]

I'm much more adept at Access vba so I really wouldn't want to attempt this for you. If you're not familiar with vba arrays, then I guess that's where to start. You can load the text file directly into the array - don't have to put on a sheet first unless maybe you want to massage it first. The array size is basically limited to your resources. Not sure I see why you'd put dissimilar data in a one dimensional array as the value in one column would seem to have nothing to do with any other column. I'd also look into TransferText but if it's possible in Excel the difficulty may be getting it directly from your http pull.

Easy for me to say I guess, but given that you can link a csv file as an Access table that you can run queries/reports on I'd say you'll spend far less time figuring out how to use Access as a solution compared to learning how to do this in Excel vba.

 

[lyrec0]

I'm much more adept at Access vba so I really wouldn't want to attempt this for you. If you're not familiar with vba arrays, then I guess that's where to start. You can load the text file directly into the array - don't have to put on a sheet first unless maybe you want to massage it first. The array size is basically limited to your resources. Not sure I see why you'd put dissimilar data in a one dimensional array as the value in one column would seem to have nothing to do with any other column. I'd also look into TransferText but if it's possible in Excel the difficulty may be getting it directly from your http pull.

Easy for me to say I guess, but given that you can link a csv file as an Access table that you can run queries/reports on I'd say you'll spend far less time figuring out how to use Access as a solution compared to learning how to do this in Excel vba.

Okay, I see. I'll have to admit that using Access for this does sound a lot more hassle-free than having to come up with a solution in VBA. It's not that I'm limited to just Excel, but I have to consider the model being used by different users on different systems. The reason I said that I prefer just Excel, is more or less a safety-measure in case the model can't be used by a user or system that doesn't have both Access and Excel installed. Although that probably doesn't occur very often anymore now many users are part of O365.

I am now going to take a look at your suggestion, how would you go about this in Access?

 

[Micron]

There is Access RunTime version for those who don't have the full version. The developer must have a full copy though. Sounds like you're going to be doing a fair bit of Googling if you're not familiar with Access. You could try Ribbon>External Data>New Data Source>From File>Text File and see what you get as a table. That might only be OK as a raw data table, but to know that, you'd have to understand db normalization. Assuming you're going down this path, here's what I recommend as a minimum re: research.

Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

Entity-Relationship Diagramming: Part I, II, III and IV

Entity-Relationship Diagramming: Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

How do I Create an Application in Microsoft Access?

How do I Create an Application in Microsoft Access?

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

Naming conventions - General: Commonly used naming conventions

MS Access Naming Conventions

_________________________________________________________ Naming Conventions. This document is aimed at the user who is unfamiliar with any naming convention. It is based upon personal experience and the interaction I have had with others. It is not a hard and fast rule. Why use a...

www.access-programmers.co.uk

What not to use in names
- Microsoft Access tips: Problem names and reserved words in Access

About Auto Numbers
- General: Use Autonumbers properly

The evils of lookup fields - The Access Web - The Evils of Lookup Fields in Tables
Table and PK design tips - Microsoft Access Tables, Primary Key Tips and Techniques
About calculated table fields - Microsoft Access tips: Calculated Fields
About Multi Value Fields - Multivalued Fields - Mendip Data Systems