Excel VBA Import Text File Help - MacRumors Forums
Register FAQ / Rules Forum Spy Search Today's Posts Mark Forums Read
Go Back   MacRumors Forums > Apple Systems and Services > Programming > Mac Programming

Reply
 
Thread Tools Search this Thread Display Modes
Old Jun 15, 2009, 11:33 AM   #1
LtRammstein
macrumors 6502a
 
Join Date: Jun 2006
Location: Denver, CO
Send a message via AIM to LtRammstein Send a message via MSN to LtRammstein
Excel VBA Import Text File Help

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.
__________________
17" White Intel iMac 1.83GHz, 250 GB HD, 2 GB RAM || iPod Touch 16GB w/ 3.0 || 15" Intel MBP 2.4GHz, 250 GB HD, 4 GB RAM
LtRammstein is offline   0 Reply With Quote
Old Jun 21, 2009, 06:29 PM   #2
ktemkin
macrumors newbie
 
Join Date: Oct 2007
Quote:
Originally Posted by LtRammstein View Post
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.
ktemkin is offline   0 Reply With Quote
Old Jun 22, 2009, 11:02 AM   #3
LtRammstein
Thread Starter
macrumors 6502a
 
Join Date: Jun 2006
Location: Denver, CO
Send a message via AIM to LtRammstein Send a message via MSN to 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?
__________________
17" White Intel iMac 1.83GHz, 250 GB HD, 2 GB RAM || iPod Touch 16GB w/ 3.0 || 15" Intel MBP 2.4GHz, 250 GB HD, 4 GB RAM
LtRammstein is offline   0 Reply With Quote
Old Jun 22, 2009, 07:12 PM   #4
ktemkin
macrumors newbie
 
Join Date: Oct 2007
Quote:
Originally Posted by LtRammstein View Post
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
ktemkin is offline   0 Reply With Quote
Old Jun 23, 2009, 12:49 AM   #5
LtRammstein
Thread Starter
macrumors 6502a
 
Join Date: Jun 2006
Location: Denver, CO
Send a message via AIM to LtRammstein Send a message via MSN to LtRammstein
Oh, I fully agree, but with M$ potentially releasing VBA stuff for Office Mac, it can potentially still be in the right place.
__________________
17" White Intel iMac 1.83GHz, 250 GB HD, 2 GB RAM || iPod Touch 16GB w/ 3.0 || 15" Intel MBP 2.4GHz, 250 GB HD, 4 GB RAM
LtRammstein is offline   0 Reply With Quote

Reply
MacRumors Forums > Apple Systems and Services > Programming > Mac Programming

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Similar Threads
thread Thread Starter Forum Replies Last Post
Need VBA macro to select and import .txt file brunwynne Mac Applications and Mac App Store 1 Jul 13, 2013 10:25 PM
TxtPub - Text file reader app by converting text to ePub automatically. Ralph Kim iPad Apps 0 Apr 26, 2013 09:13 PM
Applescript to Export text from Indesign and import to Microsoft Excel. alissat Mac Programming 1 Mar 28, 2013 11:21 AM
Importing text editor to Excel wilcco Mac Basics and Help 2 Feb 26, 2013 04:11 PM
#import or #include header file - file not found MickeyT iPhone/iPad Programming 4 Jul 2, 2012 01:58 AM

Forum Jump

All times are GMT -5. The time now is 08:10 PM.

Mac Rumors | Mac | iPhone | iPhone Game Reviews | iPhone Apps

Mobile Version | Fixed | Fluid | Fluid HD
Copyright 2002-2013, MacRumors.com, LLC