macOS Excel VBA Import Text File Help

[LtRammstein]

I just want to know if any of you can provide a good resource or some insight on how I could do this. This question is not a basic "record macro" thing, it's more than that.

Question:
I'm making a database for a large set of temperature and heat data from a building with 4 zones. The sensor go through LabVIEW that has calibration equations in it and stores this data every minute into a XLS file, once a wall clock hour is over, the file is saved with 60 points of data and continues this process. The file names are in general, "RM 115 6-15-09-00". The two 00, are the hour they are taken from. In this case, this data is of the midnight hour on 6/15/09 for the zone RM 115.

Since I'm trying to automate basically everything, I need to take the name of this file and use it in some subroutines I have already written. One being "Insert Date Header." This will take the date off the filename and fill in the proper cells with the proper time. Another subroutine will take the name of the zone and set it to the sheet name, and potentially to the filename of the newly imported data.

Any help would be great!

Lastly, I would love to do this work in OS X using FileMaker, but the system this is going on is Windows based.

 

[ktemkin]

I just want to know if any of you can provide a good resource or some insight on how I could do this. This question is not a basic "record macro" thing, it's more than that.

Question:
I'm making a database for a large set of temperature and heat data from a building with 4 zones. The sensor go through LabVIEW that has calibration equations in it and stores this data every minute into a XLS file, once a wall clock hour is over, the file is saved with 60 points of data and continues this process. The file names are in general, "RM 115 6-15-09-00". The two 00, are the hour they are taken from. In this case, this data is of the midnight hour on 6/15/09 for the zone RM 115.

Since I'm trying to automate basically everything, I need to take the name of this file and use it in some subroutines I have already written. One being "Insert Date Header." This will take the date off the filename and fill in the proper cells with the proper time. Another subroutine will take the name of the zone and set it to the sheet name, and potentially to the filename of the newly imported data.

Any help would be great!

Lastly, I would love to do this work in OS X using FileMaker, but the system this is going on is Windows based.

Need a bit more information here.

Let me see if I understand this.

This system that you have creates these hourly Excel files, with the file name encoding date and hour information. And each of these file contains 60 records of data.

You say you want to manipulate this data into a "database." What do you mean by database? Do you mean another Excel file? Do you mean an Access database? Do you mean something more robust, like SQL Server?

If, what I am understanding here is correct, it sounds to me like your database will simply contain a single table, and that table will be keyed with a date/time stamp generated from a minipulation of the file name and the minute data from the spreadsheets. The remaining fields of the table will be your actual sensor data.

Is my understanding correct.

If so, there is no reason why this can't be completely automated, in less than 25 lines of VBA code. This is not a big deal.

 

[LtRammstein]

You got it!

The database right now is Access, but it might move to SQL server.

It's been slow teaching myself VBA... Any suggestions on websites that might help?

 

[ktemkin]

You got it!

The database right now is Access, but it might move to SQL server.

It's been slow teaching myself VBA... Any suggestions on websites that might help?

If I might suggest, because this is an Apple site, and your question is purely a MS question, we should probably take this off-line.

I've sent you a private message with my eMail address. Get a hold of me and I can provide you with the code.

Ken

 

[LtRammstein]

Oh, I fully agree, but with M$ potentially releasing VBA stuff for Office Mac, it can potentially still be in the right place.