1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Importing Multiple Files

Discussion in 'Business Applications' started by GeorgeRouse, Mar 27, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. GeorgeRouse

    GeorgeRouse Thread Starter

    Joined:
    Feb 8, 2006
    Messages:
    18
    Hi,

    I have about 79 .dat files all with one column of text of the format:

    Ch0
    34
    35
    36
    36
    ... etc.


    They are all saved on my hard drive in the same folder and I would like to import all these into one Excel 2003 worksheet. I have searched the internet to try and find a solution and have been unable to get one..

    I'm a beginner at Excel so any explanations that contain Macros I am going to need a layman's guide!

    Hope someone can help me...
     
  2. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    How are the .dat files named?
    Sequential numbers?
     
  3. GeorgeRouse

    GeorgeRouse Thread Starter

    Joined:
    Feb 8, 2006
    Messages:
    18
    Well... they are results from tests taken at different angles so they are in the form:

    2.0deg.dat
    2.25deg.dat
    2.5deg.dat
    2.75deg.dat
    2.3deg.dat

    etc.
     
  4. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    I'm happy to write a Macro for you if you wish, and then provide an explanation of how it works.

    However, I need some sample data, and how you want it laid out. If you want to attach some sample/dummy information, both a .dat file and a workbook with the layout of the data once imported.
     
  5. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    As already mentioned it would be best to post at least one sample .dat file and also include a detailed description on how the data should be posted into the Excel workbook. Should each .dat file be written across its own row or should it be written down the column? More details please.

    Regards,
    Rollin
     
  6. GeorgeRouse

    GeorgeRouse Thread Starter

    Joined:
    Feb 8, 2006
    Messages:
    18
    Hi,

    Yeah, thanks for the help so far.. The data needs to be in columns with the filename in the first row and then the data following. I have attacked 2 sample data files.

    Thanks
     

    Attached Files:

  7. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Please upload a sample Excel file showing how the data you provided in the two sample .dat files should appear. Also let us know if the .dat files should be imported into the Excel workbook in any particular order.

    Regards,
    Rollin
     
  8. GeorgeRouse

    GeorgeRouse Thread Starter

    Joined:
    Feb 8, 2006
    Messages:
    18
    Here is a sample of how I want the two data files, which I previously posted, to look in the worksheet. I have incresing angles from 2 - 50 deg and they are all like the two files posted previously.

    Hope you can help me, sorry for the late reply, have been doing more testing...
     
  9. GeorgeRouse

    GeorgeRouse Thread Starter

    Joined:
    Feb 8, 2006
    Messages:
    18
    sorry, here it is...
     
  10. GeorgeRouse

    GeorgeRouse Thread Starter

    Joined:
    Feb 8, 2006
    Messages:
    18
    Previous file was too big for the site... I had to remove some of the data but I think you get the idea of what I want to do...
     

    Attached Files:

  11. GeorgeRouse

    GeorgeRouse Thread Starter

    Joined:
    Feb 8, 2006
    Messages:
    18
    also the file names have changed to make it a little easier... they are now:

    2.00deg.dat
    2.25deg.dat
    2.50deg.dat
     
  12. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    In what order should the .DAT files be imported into the workbook? Can we assume that you want the order based on the number of degrees (acending order) ???

    Regards,
    Rollin
     
  13. GeorgeRouse

    GeorgeRouse Thread Starter

    Joined:
    Feb 8, 2006
    Messages:
    18
    Yeah, I would like them in increasing numerical value, i.e. 2.00, 2.50, 2.75 ettc. till 50.00
     
  14. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    I have to admit I'm stumped!

    And now my head hurts! Hopefully Rollin can save the day, apologies GeorgeRouse.

    Or you might wanna try the Software Development section, and ask if anyone can help:
    http://forums.techguy.org/45-software-development/
     
  15. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Will all of the filenames be based on the number of degrees? Will all the .DAT files follow the exact same format? Copy the code below into a blank Excel document and run the macro and let me know how it works out for you. I've also included the code in an attached text file in case it's hard to copy and paste from the code window below.

    Code:
    Private Declare Function SendMessage Lib "USER32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
    Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias "SHBrowseForFolderA" (lpbi As BrowseInfo) As Long
    Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
    Private Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMem As Long)
    Private Const MAX_PATH = 260
    Private Const BIF_RETURNONLYFSDIRS = 1
    Private Const BIF_STATUSTEXT = 4
    Private Const WM_USER = &H400
    Private Const BFFM_INITIALIZED = 1
    Private Const BFFM_SELCHANGED = 2
    Private Const BFFM_SETSTATUSTEXTA = (WM_USER + 100)
    Private Const BFFM_SETSELECTIONA = (WM_USER + 102)
    
    Private Type BrowseInfo
        hwndOwner As Long
        pIDLRoot As Long
        pszDisplayName As String
        lpszTitle As String
        ulFlags As Long
        lpfn As Long
        lParam As Long
        iImage As Long
    End Type
    
    Private m_sDefaultFolder As String
    Public Sub BrowseFolder()
    
    frmBrowse.Show
    frmBrowse.txtDirectory.Text = ""
    
    End Sub
    
    
    
    'Displays BrowseForFolder dialog box
    Public Function BrowseForFolder(DefaultFolder As String, Optional Parent As Long = 0, Optional Caption As String = "") As String
        Dim bi As BrowseInfo
        Dim sResult As String, nResult As Long
    
        bi.hwndOwner = Parent
        bi.pIDLRoot = 0
        bi.pszDisplayName = String$(MAX_PATH, Chr$(0))
        If Len(Caption) > 0 Then
            bi.lpszTitle = Caption
        End If
        bi.ulFlags = BIF_RETURNONLYFSDIRS
        bi.lpfn = GetAddress(AddressOf BrowseCallbackProc)
        bi.lParam = 0
        bi.iImage = 0
    
        m_sDefaultFolder = DefaultFolder
    
        nResult = SHBrowseForFolder(bi)
    
        If nResult <> 0 Then
            sResult = String(MAX_PATH, 0)
            If SHGetPathFromIDList(nResult, sResult) Then
                BrowseForFolder = Left$(sResult, InStr(sResult, Chr$(0)) - 1)
            End If
    
            CoTaskMemFree nResult
        End If
    End Function
    
    
    Private Function BrowseCallbackProc(ByVal hwnd As Long, ByVal uMsg As Long, ByVal lParam As Long, ByVal lpData As Long) As Long
        Select Case uMsg
            Case BFFM_INITIALIZED
    
                If Len(m_sDefaultFolder) > 0 Then
    
                    SendMessage hwnd, BFFM_SETSELECTIONA, True, ByVal m_sDefaultFolder
                End If
        End Select
    End Function
    
    
    Private Function GetAddress(nAddress As Long) As Long
        GetAddress = nAddress
    End Function
    
    
    
    Sub ImportDat()
    
    Dim aArray() As String
    Dim vFile As String
    Dim i As Integer
    Dim j As Integer
    Dim vFirst As String
    Dim vSecond As String
    Dim vWorkbook
    Dim txtDirectory As String
    
    vWorkbook = ActiveWorkbook.Name
    txtDirectory = BrowseForFolder(txtDirectory, , "&Select Directory Containing DAT files:")
    
    If txtDirectory = "" Then
    Exit Sub
    End If
    
    vFile = Dir$(txtDirectory & "\*.dat")
    Do While vFile <> ""
    vCount = vCount + 1
    ReDim Preserve aArray(1 To vCount)
    aArray(vCount) = vFile
    vFile = Dir
    Loop
    
         For i = 1 To UBound(aArray)
           For j = i To UBound(aArray)
                If UCase(aArray(j)) < UCase(aArray(i)) Then
                    vFirst = aArray(i)
                    vSecond = aArray(j)
                    aArray(i) = vSecond
                    aArray(j) = vFirst
                End If
            Next j
        Next i
    
    For k = 1 To UBound(aArray)
    
       Workbooks.OpenText Filename:= _
            txtDirectory & "\" & aArray(k), Origin:=xlWindows, _
            StartRow:=1
            
    Workbooks(vWorkbook).Sheets(1).Range(Left(Columns(k).Address(0, 0), 2 + (k < 27)) & 1).Value = _
    aArray(k)
            
    ActiveSheet.UsedRange.Copy Destination:= _
    Workbooks(vWorkbook).Sheets(1).Range(Left(Columns(k).Address(0, 0), 2 + (k < 27)) & 2)
    
    ActiveWorkbook.Close
    
    Next k
    
    End Sub
    
    
    
    
    
    
    
    
    Regards,
    Rollin
     

    Attached Files:

  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/697763

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice