Importing various types of files

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to write my code to be able to tell the difference between a
text file, excel file, ect.? Another words, if its a text file selected have
the code import as text and if its an excel file select the code for
importing excel.

My code works fine as long as it is written to accept one or the other but
not all and the users are not sure which format they prefer to import from.
 
Is there a way to write my code to be able to tell the difference between a
text file, excel file, ect.? Another words, if its a text file selected have
the code import as text and if its an excel file select the code for
importing excel.

My code works fine as long as it is written to accept one or the other but
not all and the users are not sure which format they prefer to import from.
Click to expand...

Off the top of my flat head, I would do something like create several
import specifications (one for each file type) and then grab the file
type and maybe look up the file spec name from a table. Then you
could use some basic logic to get the right file spec for each file
type. You'd have a table of (FileType, FileSpec) and then you could
use DLookup or Trevor Best's TLookup to return the associated fileSpec
for a given file type. Then you could just pass them to the
TransferDatabase function.

(Sorry, long day of staring at about the most retarded spreadsheet
I've ever seen)
 
Simplest is to start by looking at the file extension, the characters
after the last "." in the filename.

If it's "xls", assume it's an Excel workbook. Then pass the filespec to
TransferSpreadsheet and see what happens: if it imports OK it was either
an Excel file or a Lotus one.

If the extension is "txt", "csv", "tab", "asc", assume it's a text file
and use TransferText.

If it's "htm" or "html" use TransferText acImportHTML and so on.

(All this assumes that your code really does work fine apart from
knowing what sort of file it is. There are lots of possible
complications regarding worksheet names and ranges, text file
specifications, HTML table names, etc.)
 
This is how my code is currently written:
Public Sub Import_Click()
Dim strFilter As String
Dim strInputFileName As String
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)",
"*.XLS")
' strFilter = ahtAddFilterItem(strFilter, "Access Files (*.mda,
*.mdb)", "*.MDA;*.MDB")
' strFilter = ahtAddFilterItem(strFilter, "dBASE Files (*.dbf)",
"*.DBF")
' strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)",
"*.TXT")
' strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an import file...", _
Flags:=ahtOFN_HIDEREADONLY)
MsgBox """linking to the text files""", vbInformation, "Linking"
DoCmd.TransferSpreadsheet acImport, 8, "Sessions", strInputFileName,
True
'DoCmd.transfertext acImportDelim, "Speakers and Moderators Import
Specifications", "Speakers", "", True, ""

' Import complete
Beep
MsgBox "Import Complete", vbInformation, "You're ready for the next step"
End Sub

How would I edit this code to decifer the file type?
 
As I said, you just have to look at the file extension, the characters
after the last . in the filespec.

So add some declarations:

Dim strExtension As String
Const TABLENAME As String = "Sessions"

and then after your MsgBox do something like this:

strExtension = Right(strInputFileName , _
Len(strInputFileName )-Instrrev(strInputFileName , "."))
Select Case strExtension
Case "txt"
DoCmd.TransferText acImport, "My Specification", _
TABLENAME, strInputFileName, True

Case "xls"
DoCmd.TransferSpreadsheet ...

Case "dbf"
DoCmd.TransferDatabase ...

Case "mdb", "mde"
DoCmd.TransferDatabase ...

End Select


Not that this assumes that the files the users select will actually
contain a table with the right structure. In TransferSpreadsheet, you'll
need to supply the worksheet name (e.g. "Sessions$") or else assume that
the sheet you need will always be the first one. If you're aiming for
user-proof code it will be necessary to add a lot of error checking and
error trapping.
 
Thanks John, I have never seen "case" before but I'll give it a try. I
appreciate your time and information.
 
Back
Top