I have written a rather large macro that takes data from three other
sites and, along with our own data, compile it into a massive table.
The macro works fine. (It cuts the job from about three days, down to
about 3 hours.)

But the problem I'm having is that the people who submit the report
can't send it to me in the same format in any given month. (I know, I
should be able to simply tell them what I want, but I'm not getting any
cooperation from their managers.) I have to sit down and either tweak
the data or the macro so the data can be processed. It has the right
information, but columns may be out of order, include additional
information I don't need. And, I might get a Excel file, or a Word
file or a text file.

So, I've decided to try and write a Data Cleaner to set the data in the
order the Macro needs. So my question is, can I differenciate between
Excel and Word files. Is there something in the header that I can use
to load the correct object?

Thanks in advance!

Hi David,

Not sure if this is any help to you, but I wrote a little macro a whil
ago that determines file types in a given directory by their extension
It uses activeX so you may need to create some references in you
project:

Sub chFType()
Set fObj = CreateObject("scripting.filesystemobject")
Set dObj = CreateObject("wscript.shell")
dObj.Run "c:\winnt\system32\cmd.exe /c dir c:\ c:\testfTypes.txt", 0
True


Set rFile = fObj.opentextfile("C:\testfTypes.txt", 1, False)

Do Until rFile.atendofstream
currLine = rFile.readLine
getDot = InStr(1, currLine, ".")
If getDot 0 Then
dotExists = Mid(currLine, getDot, 4)
End If

If dotExists = ".xls" Then
MsgBox "I found an excel file! Its called " & currLine
ElseIf dotExists = ".txt" Then
MsgBox "I found a text file! Its called "
currLine
ElseIf dotExists = ".doc" Then
MsgBox "I found a word document! Its calle
" & currLine
Else
MsgBox "This file is not word
Excel or Text!"

End If


Loop

End Sub


Just thinking you could fiddle this code to do different thing
depending on the file type. As you can see there's a few 'ifs' in ther
to tell you what file types are in the directory.

Hope that helps
Jo

--
LFCFa
-----------------------------------------------------------------------
LFCFan's Profile: http://www.excelforum.com/member.php...fo&userid=3748
View this thread: http://www.excelforum.com/showthread.php?threadid=57136

David,
Can you trust the file extension ?
If so an it's only a few that you are dealing with, a simple Select Case of
the extension may be enough.
Once you can determine which data source to use, query it with ADO, getting
the fields in the RS in the order you wish. This assumes your is
ADO-friendly and obviously won't work for Word directly until they are
..SaveAs csv format.

Whilst you can open a Word file in Excel, what you get is complete rubbish.
You can Copy/Paste from Word to Excel, so if you have table mixed with text
this may be option.
You can also save the Word files as HTML, the web query them from Excel.

Some ideas; it all depends on how your data is laid out

NickHK

"David Sisson" wrote in message
ups.com...
I have written a rather large macro that takes data from three other
sites and, along with our own data, compile it into a massive table.
The macro works fine. (It cuts the job from about three days, down to
about 3 hours.)

But the problem I'm having is that the people who submit the report
can't send it to me in the same format in any given month. (I know, I
should be able to simply tell them what I want, but I'm not getting any
cooperation from their managers.) I have to sit down and either tweak
the data or the macro so the data can be processed. It has the right
information, but columns may be out of order, include additional
information I don't need. And, I might get a Excel file, or a Word
file or a text file.

So, I've decided to try and write a Data Cleaner to set the data in the
order the Macro needs. So my question is, can I differenciate between
Excel and Word files. Is there something in the header that I can use
to load the correct object?

Thanks in advance!

David,

I tinkered with my code a bit, it will now open any of the 3 file types
in the right app. Feel free to mod it for your own use.

Sub chFType()
Set fObj = CreateObject("scripting.filesystemobject")
Set dObj = CreateObject("wscript.shell")
dObj.Run "c:\winnt\system32\cmd.exe /c dir c:\ /D c:\testfTypes.txt",
0, True


Set rFile = fObj.opentextfile("C:\testfTypes.txt", 1, False)

Do Until rFile.atendofstream
currLine = rFile.readLine
getDot = InStr(1, currLine, ".")
lookRight = Right(currLine, 1)
If getDot 0 And lookRight < "." Then
dotExists = Mid(currLine, getDot, 4)
If dotExists = ".xls" Then
xlsMsg = MsgBox("I found an excel file! Its called " & currLine
& Chr(13) & Chr(13) & "Would you like to open it?", vbYesNo)
If xlsMsg = vbYes Then
dObj.Run ("c:\winnt\system32\cmd.exe /c start excel c:\") &
currLine
End If
ElseIf dotExists = ".txt" Then
txtAsk = MsgBox("I found a text file! Its called " &
currLine & Chr(13) & Chr(13) & "Would you like to Open it?", vbYesNo)
If txtAsk = vbYes Then
dObj.Run ("c:\winnt\system32\cmd.exe /c start
notepad.exe c:\") & currLine
End If
ElseIf dotExists = ".doc" Then
wrdAsk = MsgBox("I found a word document!
Its called " & currLine & Chr(13) & Chr(13) & "Would you like to open
it?", vbYesNo)
If wrdAsk = vbYes Then
dObj.Run
("c:\winnt\system32\cmd.exe /c start winword c:\") & currLine
End If
Else
MsgBox "The file: " & currLine & " is not word, Excel or Text!"
End If
End If


Loop

End Sub

Cheers
Joe


--
LFCFan
------------------------------------------------------------------------
LFCFan's Profile: http://www.excelforum.com/member.php...o&userid=37484
View this thread: http://www.excelforum.com/showthread...hreadid=571368