Please help with an import code (this is long)

[cedar]

Hi all-
I hope someone has enough patience and tenacity to get through this one!
I received this code from someone who was trying to help me create an import feature into my database. Unfortunately, I'm not sure how to get it to fully work, and I haven't been able to contact the person who sent it to me. I am a serious newbie to code, and could use any pointers to make this thing work....

Okay - the problem is, this module was sent to me, with a form - on the form was a command button (linked to the module), that brought up the dialog box that allows the user to navigate to the file they want to import. That all works fine....but then, it doesn't actually import the file...

The guy who sent this to me inclueded notes throughout the code, but I'm sure what it is exactly that I need to do from here on out.

If anyone can look at this, and tell me what I need to do to get this to work (or at least make more sense!) I'd greatly appreciate it!

Here it is, thanks!

Option Compare Database
Option Explicit


Type tagOPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
strFilter As String
strCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
strFile As String
nMaxFile As Long
strFileTitle As String
nMaxFileTitle As Long
strInitialDir As String
strTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
strDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean
Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean
Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long
Global Const ahtOFN_READONLY = &H1
Global Const ahtOFN_OVERWRITEPROMPT = &H2
Global Const ahtOFN_HIDEREADONLY = &H4
Global Const ahtOFN_NOCHANGEDIR = &H8
Global Const ahtOFN_SHOWHELP = &H10
Global Const ahtOFN_NOVALIDATE = &H100
Global Const ahtOFN_ALLOWMULTISELECT = &H200
Global Const ahtOFN_EXTENSIONDIFFERENT = &H400
Global Const ahtOFN_PATHMUSTEXIST = &H800
Global Const ahtOFN_FILEMUSTEXIST = &H1000
Global Const ahtOFN_CREATEPROMPT = &H2000
Global Const ahtOFN_SHAREAWARE = &H4000
Global Const ahtOFN_NOREADONLYRETURN = &H8000
Global Const ahtOFN_NOTESTFILECREATE = &H10000
Global Const ahtOFN_NONETWORKBUTTON = &H20000
Global Const ahtOFN_NOLONGNAMES = &H40000
Global Const ahtOFN_EXPLORER = &H80000
Global Const ahtOFN_NODEREFERENCELINKS = &H100000
Global Const ahtOFN_LONGNAMES = &H200000
Function TestIt()
Dim strFilter As String
Dim lngFlags As Long
strFilter = ahtAddFilterItem(strFilter, "Access Files (*.mda, *.mdb)", "*.MDA;*.MDB&quot
strFilter = ahtAddFilterItem(strFilter, "dBASE Files (*.dbf)", "*.DBF&quot
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.XLS&quot
strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.TXT&quot
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*&quot
MsgBox "You selected: " & ahtCommonFileOpenSave(InitialDir:="C:\", Filter:=strFilter, FilterIndex:=1, Flags:=lngFlags, DialogTitle:="Select Data to Import&quot
' Since you passed in a variable for lngFlags,
' the function places the output flags value in the variable.
Debug.Print Hex(lngFlags)
End Function
Function GetOpenFile(Optional varDirectory As Variant, Optional varTitleForDialog As Variant) As Variant
' Here's an example that gets an Access database name.
Dim strFilter As String
Dim lngFlags As Long
Dim varFileName As Variant
' Specify that the chosen file must already exist,
' don't change directories when you're done
' Also, don't bother displaying
' the read-only box. It'll only confuse people.
lngFlags = ahtOFN_FILEMUSTEXIST Or ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR
If IsMissing(varDirectory) Then
varDirectory = ""
End If
If IsMissing(varTitleForDialog) Then
varTitleForDialog = ""
End If

' Define the filter string and allocate space in the "c"
' string Duplicate this line with changes as necessary for
' more file templates.
strFilter = ahtAddFilterItem(strFilter, "Access (*.mdb)", "*.MDB;*.MDA&quot
' Now actually call to get the file name.
varFileName = ahtCommonFileOpenSave(penFile:=True, InitialDir:=varDirectory, Filter:=strFilter, Flags:=lngFlags, DialogTitle:=varTitleForDialog)
If Not IsNull(varFileName) Then
varFileName = TrimNull(varFileName)
End If
GetOpenFile = varFileName
End Function
Function ahtCommonFileOpenSave( _
Optional ByRef Flags As Variant, Optional ByVal InitialDir As Variant, Optional ByVal Filter As Variant, Optional ByVal FilterIndex As Variant, Optional ByVal DefaultExt As Variant, Optional ByVal FileName As Variant, Optional ByVal DialogTitle As Variant, Optional ByVal hwnd As Variant, Optional ByVal OpenFile As Variant) As Variant
' This is the entry point you'll use to call the common
' file open/save dialog. The parameters are listed
' below, and all are optional.
'
' In:
' Flags: one or more of the ahtOFN_* constants, OR'd together.
' InitialDir: the directory in which to first look
' Filter: a set of file filters, set up by calling
' AddFilterItem. See examples.
' FilterIndex: 1-based integer indicating which filter
' set to use, by default (1 if unspecified)
' DefaultExt: Extension to use if the user doesn't enter one.
' Only useful on file saves.
' FileName: Default value for the file name text box.
' DialogTitle: Title for the dialog.
' hWnd: parent window handle
' OpenFile: Boolean(True=Open File/False=Save As)
' Out:
' Return Value: Either Null or the selected filename
Dim OFN As tagOPENFILENAME
Dim strFileName As String
Dim strFileTitle As String
Dim fResult As Boolean
' Give the dialog a caption title.
If IsMissing(InitialDir) Then InitialDir = CurDir
If IsMissing(Filter) Then Filter = ""
If IsMissing(FilterIndex) Then FilterIndex = 1
If IsMissing(Flags) Then Flags = 0&
If IsMissing(DefaultExt) Then DefaultExt = ""
If IsMissing(FileName) Then FileName = ""
If IsMissing(DialogTitle) Then DialogTitle = ""
If IsMissing(hwnd) Then hwnd = Application.hWndAccessApp
If IsMissing(OpenFile) Then OpenFile = True
' Allocate string space for the returned strings.
strFileName = Left(FileName & String(256, 0), 256)
strFileTitle = String(256, 0)
' Set up the data structure before you call the function
With OFN
.lStructSize = Len(OFN)
.hwndOwner = hwnd
.strFilter = Filter
.nFilterIndex = FilterIndex
.strFile = strFileName
.nMaxFile = Len(strFileName)
.strFileTitle = strFileTitle
.nMaxFileTitle = Len(strFileTitle)
.strTitle = DialogTitle
.Flags = Flags
.strDefExt = DefaultExt
.strInitialDir = InitialDir
.hInstance = 0
.strCustomFilter = ""
.nMaxCustFilter = 0
.lpfnHook = 0
'New for NT 4.0
.strCustomFilter = String(255, 0)
.nMaxCustFilter = 255
End With
' This will pass the desired data structure to the
' Windows API, which will in turn it uses to display
' the Open/Save As Dialog.
If OpenFile Then
fResult = aht_apiGetOpenFileName(OFN)
Else
fResult = aht_apiGetSaveFileName(OFN)
End If

' The function call filled in the strFileTitle member
' of the structure. You'll have to write special code
' to retrieve that if you're interested.
If fResult Then
' You might care to check the Flags member of the
' structure to get information about the chosen file.
' In this example, if you bothered to pass in a
' value for Flags, we'll fill it in with the outgoing
' Flags value.
If Not IsMissing(Flags) Then Flags = OFN.Flags
ahtCommonFileOpenSave = TrimNull(OFN.strFile)
Else
ahtCommonFileOpenSave = vbNullString
End If
End Function
Function ahtAddFilterItem(strFilter As String, strDescription As String, Optional varItem As Variant) As String
' Tack a new chunk onto the file filter.
' That is, take the old value, stick onto it the description,
' (like "Databases&quot, a null character, the skeleton
' (like "*.mdb;*.mda&quot and a final null character.

If IsMissing(varItem) Then varItem = "*.*"
ahtAddFilterItem = strFilter & strDescription & vbNullChar & varItem & vbNullChar
End Function
Private Function TrimNull(ByVal strItem As String) As String
Dim intPos As Integer
intPos = InStr(strItem, vbNullChar)
If intPos > 0 Then
TrimNull = Left(strItem, intPos - 1)
Else
TrimNull = strItem
End If
End Function

 

[TheInsider]

Hmmm... this is hardly a piece of code that I would throw at a VBA newbie and expect them to do anything with it. At a glance... He is using API to open a file with the Open Common Dialog box. I don't have the time to read through the entire code. You need to post what you are trying to accomplish. There is probably a very simple solution. Rob Marriott
rob@career-connections.net

 

[cedar]

Hey-
I am trying to create a search/import function on my database....I don't have Access ODE, otherwise I would use the Common Dialog control (activeX)...
Basically, I want the user to be able to search for and import files into this database ----I wanted something a little more straightforward than the built-in Import function, because this database will be used by people with little to no access experience....But, perhaps there isn't a better way around it.

 

[petermeachem]

Why can't you use the common dialog control. I don't understand. Peter Meachem
peter@accuflight.com

 

[TheInsider]

Hi,
My question was a little ambiguous so I'll elaborate... You said:

"I want the user to be able to search for and import files into this database"

My question is: What are you importing? Tables or records? ie. Are you trying to import the records from TableX in an external database to TableX in the local database or are you trying to physically import an entire table into your database? For the latter, use the TransferDatabase method. Are you asking about how to write common dialog box code, the import process code, or both? Rob Marriott
rob@career-connections.net

 

[cedar]

Hi all-
Peter- I can't use the Common Dialog control because it is only available to those using Access ODE (developer's edition)....whereas I have the professional version....Trust me, I've been through this with the folks at microsoft.

Rob - I'm importing data from a variety of formats....Excel spreadsheets, dBase files, text files, and other Access tables will be the primary data formats.
-I started working with the TransferText, but got sidetracked by this code (above).
-I just want a simple, user friendly method for folks to use for importing data into the database. Once imported, the data will be appended to one of several tables (depending on the data). I could just explain to them how to do an import, but I'd rather make it clearer to them with some kind of dialog box that pops up.
-I'm sure I'm making this 10X more complicated than it needs to be. I hope I answered your questions
-thanks for any help!