How to import multiple txt files to excel and then to access database?

[JonRok]

Hello everyone!

I am new to the forum and a begginer in VB Access.

Is it possible to create a macro in Access to open a folderpicker dialog box, import all the .txt files to an Excel file to perform calculations and next to import the data to the Access database?

So far I have acomplished the importation from txt files to excel and from excel to access but I have designed two separated macros to perform this task (one in Excel and the other in Access) and for a single file at a time.

The problem to this structure is that i need to import several text files and i would also need to create the same number of Excel files for the last importation to Access.

So I was wondering if I could create a single macro in Access to select a folder (containing multiple text files), instead of a single text file and in a while loop (maybe) import to excel, perform calculations and then import to Access each file of the selected folder and not create several excel files, since i just need one to perform the calculations.

Sorry for the long post. Thanks in advance

Best regards,

Jonrok

 

[Galaxiom]

Is there a reason for involving Excel?

Access can read suitably regularly formatted text files directly as tables.

With a bit of ingenuity, VBA can be used to read and interpret any text file data structure.

Access can do any of the maths that Excel can do.

FWIW. So can Word!

 

[JonRok]

I am using Excel mainly to execute linear interpolations to correct data from the text files.

The interpolations are based in multiples if functions, extrating values from a table in excel to correct a single value (the imported text value). Can Access perform the "if" functions as well as Excel?

This is an example of one of the linear interpolations I used in Excel:

[FONT=&quot]=IF($C$3=0;1;IF($C$3<=10;FC!G4+(((FC!G3-FC!G4)*('Data Tables'!$C$3-FC!C4))/(FC!C3-FC!C4));IF($C$3<=20;FC!G5+(((FC!G4-FC!G5)*('Data Tables'!$C$3-FC!C5))/(FC!C4-FC!C5));IF($C$3<=30;FC!G6+(((FC!G5-FC!G6)*('Data Tables'!$C$3-FC!C6))/(FC!C5-FC!C6));IF($C$3<=40;FC!G7+(((FC!G6-FC!G7)*('Data Tables'!$C$3-FC!C7))/(FC!C6-FC!C7));IF($C$3<=50;FC!G8+(((FC!G7-FC!G8)*('Data Tables'!$C$3-FC!C8))/(FC!C7-FC!C8));IF($C$3<=60;FC!G9+(((FC!G8-FC!G9)*('Data Tables'!$C$3-FC!C9))/(FC!C8-FC!C9));IF($C$3<=70;FC!G10+(((FC!G9-FC!G10)*('Data Tables'!$C$3-FC!C10))/(FC!C9-FC!C10));IF($C$3<=80;FC!G11+(((FC!G10-FC!G11)*('Data Tables'!$C$3-FC!C11))/(FC!C10-FC!C11));IF($C$3<=90;FC!G12+(((FC!G11-FC!G12)*('Data Tables'!$C$3-FC!C12))/(FC!C11-FC!C12));IF($C$3<=100;FC!G13+(((FC!G12-FC!G13)*('Data Tables'!$C$3-FC!C13))/(FC!C12-FC!C13));IF($C$3<=110;FC!G14+(((FC!G13-FC!G14)*('Data Tables'!$C$3-FC!C14))/(FC!C13-FC!C14));IF($C$3<=120;FC!G15+(((FC!G14-FC!G15)*('Data Tables'!$C$3-FC!C15))/(FC!C14-FC!C15));IF($C$3<=130;FC!G16+(((FC!G15-FC!G16)*('Data Tables'!$C$3-FC!C16))/(FC!C15-FC!C16));IF($C$3<=140;FC!G17+(((FC!G16-FC!G17)*('Data Tables'!$C$3-FC!C17))/(FC!C16-FC!C17));IF($C$3<=150;FC!G18+(((FC!G17-FC!G18)*('Data Tables'!$C$3-FC!C18))/(FC!C17-FC!C18));IF($C$3<=160;FC!G19+(((FC!G18-FC!G19)*('Data Tables'!$C$3-FC!C19))/(FC!C18-FC!C19));IF($C$3<=170;FC!G20+(((FC!G19-FC!G20)*('Data Tables'!$C$3-FC!C20))/(FC!C19-FC!C20));IF($C$3<=180;FC!G21+(((FC!G20-FC!G21)*('Data Tables'!$C$3-FC!C21))/(FC!C20-FC!C21));IF($C$3<=190;FC!G22+(((FC!G21-FC!G22)*('Data Tables'!$C$3-FC!C22))/(FC!C21-FC!C22));IF($C$3<=200;FC!G23+(((FC!G22-FC!G23)*('Data Tables'!$C$3-FC!C23))/(FC!C22-FC!C23));"Off-Scale")))))))))))))))))))))[/FONT]

Thanks!

 

[Galaxiom]

IF in Access is a different structure called IIF but there are many other ways to perform mathematical and logical operations. Your interpolation could probably be much better expressed as a custom function using VBA in Excel or Access.

What is the ultimate presentation goal for your data? Once you get into VBA Excel can also do most of what Access can.

Any of the Office applications can drive each other. They can open hidden sessions of any of the Office applications and script any action within them. If most of what you have is already in Excel you could continue development there if it can meet your requirements.

 

[JonRok]

Any of the Office applications can drive each other. They can open hidden sessions of any of the Office applications and script any action within them. If most of what you have is already in Excel you could continue development there if it can meet your requirements

Thank you Galaxiom. That is good news, and how can i execute a hidden session of Excel from VBA Access?

I will try to develop the IIF function in Access to correct data directly from the text files.

About the other issue, is it possible to select a folder and execute a "while" loop for importing each text file? I have the code for doing the import for a single text file.

 

[gemma-the-husky]

part of the problem is that importing into excel can cause problems with the data in some cases. access/excel make "guesses" as to the type of data, and sometimes get it wrong, causing loss of data.

I would never import into excel unless there was no alternative. if you have a csv or other raw data file, I would use that directly, without any question.


----
re a loop.

the simplest way is with dir.
dir returns files until no more files are found

filename = dir("somepath\*.csv")
while filename<>""
    process filename
    filename = dir()
wend

unfortunately dir cannot process subfolders as well, as it is not recursive, so you need a different technique if you have subfolders