Importing Spreadsheets

[Glen]

I presently use the following code to import an Excel Spreadsheet:

DoCmd.TransferS preadsheet acImport, SpreadsheetType :=8, _
TableName:="tbl ImportPlayerInf ormation", _
FileName:="C:\P layerInformatio n\ExportPlayerI nformation.xls" , _
Hasfieldnames:= True

Is there a way to not have the speadsheet name defined in code but to
go a specific directory and select a speadsheet to import. I import
the full contents of the spreadsheet and no matter what the
spreadsheet is called they all have the same structure and field
names?

[Phil Stanton]

Paste the stuff below as a new module. Haven't the foggiest how it works,
but if you key in
FindFile("XXX", "YYY") it opens the folder dialogue box.
You can then hold the picked file as a global variable say "FileFound$ "
Then your transferdatabas e becomes
DoCmd.TransferS preadsheet acImport, SpreadsheetType :=8, _

TableName:="tbl ImportPlayerInf ormation", _
FileName:="File Found,_
Hasfieldnames:= True

Phil
Option Compare Database 'Use database order for string comparisons
Option Explicit ' Require variables to be declared before being
used.

Declare Function GetOpenFileName Lib "comdlg32.d ll" Alias _
"GetOpenFileNam eA" (pOpenfilename As OpenFilename) As Boolean
Declare Function GetSaveFileName Lib "comdlg32.d ll" Alias _
"GetSaveFileNam eA" (pOpenfilename As OpenFilename) As Boolean

Type MSA_OPENFILENAM E
' Filter string used for the Open dialog filters.
' Use MSA_CreateFilte rString() to create this.
' Default = All Files, *.*
strFilter As String
' Initial Filter to display.
' Default = 1.
lngFilterIndex As Long
' Initial directory for the dialog to open in.
' Default = Current working directory.
strInitialDir As String
' Initial file name to populate the dialog with.
' Default = "".
strInitialFile As String
strDialogTitle As String
' Default extension to append to file if user didn't specify one.
' Default = System Values (Open File, Save File).
strDefaultExten sion As String
' Flags (see constant list) to be used.
' Default = no flags.
lngFlags As Long
' Full path of file picked. When the File Open dialog box is
' presented, if the user picks a nonexistent file,
' only the text in the "File Name" box is returned.
strFullPathRetu rned As String
' File name of file picked.
strFileNameRetu rned As String
' Offset in full path (strFullPathRet urned) where the file name
' (strFileNameRet urned) begins.
intFileOffset As Integer
' Offset in full path (strFullPathRet urned) where the file extension
begins.
intFileExtensio n As Integer
End Type

Const ALLFILES = "All Files"

Type OpenFilename
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilt er As Long
nMaxCustrFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustrData As Long
lpfnHook As Long
lpTemplateName As Long
End Type

Const OFN_ALLOWMULTIS ELECT = &H200
Const OFN_CREATEPROMP T = &H2000
Const OFN_EXPLORER = &H80000
Const OFN_FILEMUSTEXI ST = &H1000
Const OFN_HIDEREADONL Y = &H4
Const OFN_NOCHANGEDIR = &H8
Const OFN_NODEREFEREN CELINKS = &H100000
Const OFN_NONETWORKBU TTON = &H20000
Const OFN_NOREADONLYR ETURN = &H8000
Const OFN_NOVALIDATE = &H100
Const OFN_OVERWRITEPR OMPT = &H2
Const OFN_PATHMUSTEXI ST = &H800
Const OFN_READONLY = &H1
Const OFN_SHOWHELP = &H10

Function FindFile(strSea rchPath$, fname$) As String
' Displays the Open dialog box for the user to locate
' the Northwind database. Returns the full path to Northwind.

Dim msaof As MSA_OPENFILENAM E
Dim Extension$

' Set options for the dialog box.
msaof.strDialog Title = "Where Is " & fname & " ?"
msaof.strInitia lDir = strSearchPath
msaof.strFilter = MSA_CreateFilte rString("." & Right$(fname, 3), "",
"*.*")
msaof.strDefaul tExtension = "." & Right$(fname, 3)
msaof.strInitia lFile = fname

' Call the Open dialog routine.
MSA_GetOpenFile Name msaof

' Return the path and file name.
FindFile = Trim(msaof.strF ullPathReturned )

End Function
Function MSA_CreateFilte rString(ParamAr ray varFilt() As Variant) As String
' Creates a filter string from the passed in arguments.
' Returns "" if no argumentss are passed in.
' Expects an even number of argumentss (filter name, extension), but
' if an odd number is passed in, it appends "*.*".

Dim strFilter As String
Dim intRet As Integer
Dim intNum As Integer

intNum = UBound(varFilt)
If (intNum <> -1) Then
For intRet = 0 To intNum
strFilter = strFilter & varFilt(intRet) & vbNullChar
Next
If intNum Mod 2 = 0 Then
strFilter = strFilter & "*.*" & vbNullChar
End If

strFilter = strFilter & vbNullChar
Else
strFilter = ""
End If
MSA_CreateFilte rString = strFilter
End Function

Private Function MSA_GetOpenFile Name(msaof As MSA_OPENFILENAM E) As Integer
' ******
' Opens the Open dialog.

Dim of As OpenFilename
Dim intRet As Integer

MSAOF_to_OF msaof, of
intRet = GetOpenFileName (of)
If intRet Then
OF_to_MSAOF of, msaof
End If
MSA_GetOpenFile Name = intRet
End Function

Private Sub OF_to_MSAOF(of As OpenFilename, msaof As MSA_OPENFILENAM E)
' ******
' This sub converts from the Win32 structure to the Microsoft Access
structure.

msaof.strFullPa thReturned = Left(of.lpstrFi le, InStr(of.lpstrF ile,
vbNullChar) - 1)
msaof.strFileNa meReturned = of.lpstrFileTit le
msaof.intFileOf fset = of.nFileOffset
msaof.intFileEx tension = of.nFileExtensi on
End Sub

Private Sub MSAOF_to_OF(msa of As MSA_OPENFILENAM E, of As OpenFilename)
' ******
' This sub converts from the Microsoft Access structure to the Win32
structure.

Dim strFile As String * 512

' Initialize some parts of the structure.
of.hwndOwner = Application.hWn dAccessApp
of.hInstance = 0
of.lpstrCustomF ilter = 0
of.nMaxCustrFil ter = 0
of.lpfnHook = 0
of.lpTemplateNa me = 0
of.lCustrData = 0

If msaof.strFilter = "" Then
of.lpstrFilter = MSA_CreateFilte rString(ALLFILE S)
Else
of.lpstrFilter = msaof.strFilter
End If
of.nFilterIndex = msaof.lngFilter Index

of.lpstrFile = msaof.strInitia lFile _
& String(512 - Len(msaof.strIn itialFile), 0)
of.nMaxFile = 511

of.lpstrFileTit le = String(512, 0)
of.nMaxFileTitl e = 511

of.lpstrTitle = msaof.strDialog Title

of.lpstrInitial Dir = msaof.strInitia lDir

of.lpstrDefExt = msaof.strDefaul tExtension

of.flags = msaof.lngFlags

of.lStructSize = Len(of)
End Sub

"Glen" <gl**********@s haw.ca> wrote in message
news:30******** *************** **@posting.goog le.com... I presently use the following code to import an Excel Spreadsheet:

DoCmd.TransferS preadsheet acImport, SpreadsheetType :=8, _
TableName:="tbl ImportPlayerInf ormation", _
FileName:="C:\P layerInformatio n\ExportPlayerI nformation.xls" , _
Hasfieldnames:= True

Is there a way to not have the speadsheet name defined in code but to
go a specific directory and select a speadsheet to import. I import
the full contents of the spreadsheet and no matter what the
spreadsheet is called they all have the same structure and field
names?

[MGFoster]

Glen wrote:

I presently use the following code to import an Excel Spreadsheet:

DoCmd.TransferS preadsheet acImport, SpreadsheetType :=8, _
TableName:="tbl ImportPlayerInf ormation", _
FileName:="C:\P layerInformatio n\ExportPlayerI nformation.xls" , _
Hasfieldnames:= True

Is there a way to not have the speadsheet name defined in code but to
go a specific directory and select a speadsheet to import. I import
the full contents of the spreadsheet and no matter what the
spreadsheet is called they all have the same structure and field
names?

Leave the File Name parameter blank, you will be prompted for the file
name (a File Open dialog box).

--
MGFoster:::mgf
Oakland, CA (USA)

[Glen Johnson]

Thanks for the reply, however

I first tried to leave the FileNmae:= as this which caused a compile
error.

I have removed the file name and installed "".

FileName:=""

Nothing happened.

I removed the FileName:= completely, nothing happened.

What am I missing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're not doing anything wrong. I was wrong! Apparently I was
thinking of another method. Sorry.

- --
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP77/FIechKqOuFEgEQI HTQCguAh5g71ABH akJAE13mq/Qe2N4qMAn2eA
Gepb+kgZ4wnZeLu LiI6w2da3
=XhYe
-----END PGP SIGNATURE-----
Glen Johnson wrote:

Thanks for the reply, however

I first tried to leave the FileNmae:= as this which caused a compile
error.

I have removed the file name and installed "".

FileName:=""

Nothing happened.

I removed the FileName:= completely, nothing happened.

What am I missing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!