Excel Importing Multiple Files

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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

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.
 
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.
 
Joined
Sep 4, 2003
Messages
4,916
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
 

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
 

Attachments

Joined
Sep 4, 2003
Messages
4,916
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
 

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

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

Attachments

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
 
Joined
Sep 4, 2003
Messages
4,916
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
 

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
 
Joined
Sep 4, 2003
Messages
4,916
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
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top