Importing Multiple Files
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Importing Multiple Files

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.

  2. #2
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    799
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  4. #4
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    799
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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..



User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com