Importing Multiple Files

[Zahra]

Hiya, I'm using MS Excel 97 to pre-process some data for input into a Neural Network. The data I have at the moment is in ASCII form. I need to develop a macro to open about 300 different asci files with two columns separated by spaces. This data needs to be formatted into two separate columns and then normalised. I'm not sure what form the macro would take. Do you have any tips for opening multiple documents and performing this transformation? Could someone advise on how to use the DIR function in VB to create a loop?

Specifically, when I try to import the ASCII files a text import wizard pops up which I cannot include in the macro as the macro would then specify the particular file I'm importing and not be generic enough. I'm using this wizard to divide the data into two columns so it is necessary for what I need to do. Any clues on how to get around this problem? Many thanks.

[smozgur]

You can do a search in your directory by an API Function help then run your code (import of ASCII file) with the return value which returned search operation. Here is a sample code, just copy and paste it into your worksheet module and come over the FindFiles sub then press F5 to see whats going on.

There is a variable called as "fileinfo" in FindFiles sub, change it according to your path and file extention.


Private Declare Function FindNextFile Lib "kernel32.dll" Alias "FindNextFileA" (ByVal hFindFile As Long, lpFindFileData As WIN32_FIND_DATA) As Long
Private Declare Function FindFirstFile Lib "kernel32.dll" Alias "FindFirstFileA" (ByVal lpFileName As String, lpFindFileData As WIN32_FIND_DATA) As Long
Private Declare Function FindClose Lib "kernel32.dll" (ByVal hFindFile As Long) As Long
Private Type FILETIME
dwLowDateTime As Long
dwHighDateTime As Long
End Type
Private Type WIN32_FIND_DATA
dwFileAttributes As Long
ftCreationTime As FILETIME
ftLastAccessTime As FILETIME
ftLastWriteTime As FILETIME
nFileSizeHigh As Long
nFileSizeLow As Long
dwReserved0 As Long
dwReserved1 As Long
cFileName As String * 260
cAlternate As String * 14
End Type

Private Sub FindFiles()
'Modified code : Reference http://www.vbapi.com
Dim hsearch As Long
Dim findinfo As WIN32_FIND_DATA
Dim success As Long
Dim buffer As String
Dim retval As Long

Dim fileinfo as string 'User defined

fileinfo="C:WindowsDesktopFileDir*.ext"

hsearch = FindFirstFile(fileinfo, findinfo)
If hsearch = -1 Then
Exit Sub
End If
Do
buffer = Left(findinfo.cFileName, InStr(findinfo.cFileName, vbNullChar) - 1)
Call TransferFile(buffer)
success = FindNextFile(hsearch, findinfo)
Loop Until success = 0
retval = FindClose(hsearch)
End Sub

Private Sub TransferFile(ASCIIFileName As String)
MsgBox ASCIIFileName
End Sub

Let me know if you need feature help.

You can visit TheWordExpert for VBA help and also other office applications

regards
suat



[ This Message was edited by: smozgur on 2002-03-16 16:41 ]

[ This Message was edited by: smozgur on 2002-03-16 16:42 ]

[Zahra]

Hiya,

Thanks for your advice. I'm afraid I'm a bit of a novice to VB coding and don't really know what you mean. Could you perhaps give some more basic instructions? Thanks again!

[smozgur]

Basically, code looks up the folder you selected for the files you specified with extention. After this it is easy to manage what you want to do.

If you can send me a few ASCII files and also loading rules data within them into excel sheet then i can show you what this code does..