Importing data from .xlsx files

[F]

I save a .xlsx file holding results from a data logger each day. The
files are all saved into the same folder.

I have a workbook (Excel 2010) which holds the accumulating data from
rows 10 to 206 in columns A and C in each of these files and which
interprets it.

Rather than have to open the new data logger file each day, manually
copy the relevant columns and rows and then paste them into the workbook
below the previously imported data, I would prefer the task to be
automated. Is this possible in full, or at least partially?

TIA

 

[Stan Brown]

Rather than have to open the new data logger file each day, manually
copy the relevant columns and rows and then paste them into the workbook
below the previously imported data, I would prefer the task to be
automated. Is this possible in full, or at least partially?

Sure, you can write a VBA macro called Workbook_Open in a module in
your master workbook. Then whenever you open the workbook it will
run automatically.

If you don't know VBA, I think you'll want to get some local help.
Refining your problem requirements and then creating the code is, I
think, too complex for a newsgroup.

 

[Gord Dibben]

See Ron de Bruin's site for code.

http://www.rondebruin.nl/copy1.htm

You want to scroll down the page to find the code for.................

"What if the Database sheet is in another workbook"

Aside from changing sheet and workbook names to suit you will have to
alter the SourceRange parameters.............

Set SourceRange =
ThisWorkbook.Sheets("Sheet1").Range("A10:A206,C10:C206")


Gord

 

[GS]

It happens that F formulated :

I save a .xlsx file holding results from a data logger each day. The files
are all saved into the same folder.

I have a workbook (Excel 2010) which holds the accumulating data from rows 10
to 206 in columns A and C in each of these files and which interprets it.

Rather than have to open the new data logger file each day, manually copy the
relevant columns and rows and then paste them into the workbook below the
previously imported data, I would prefer the task to be automated. Is this
possible in full, or at least partially?

TIA

Most data logger software (Like MadgeTech, for example) will generate
other data output file types like CSV, TXT, DAT, or XML (for pocket
PCs, etc) that you should be able to use standard VB file I/O
procedures to read the data and 'dump' it into any worksheet at any row
you specify. (I currently do this for filtering out non-relavent
readings lines based on client criteria for what range of data they
want to analyze)

Alternatively, you can use ADODB (if you don't want to open every
source file) to read the data into a recordset and 'dump' that data
into your worksheet.

With either approach, you don't have to physically open any source
files. With ADODB you don't even need to open the target workbook to
update the data because you can use its UPDATE function to add new
data.

 

[F]

Thanks for the suggestions, I'll sit down and work through them!