Hi Gary,
Okay, makes sense. These points were not clear in your original post.
Are you able to send me a copy of your database, and some password protected Excel files? You can
substitute dummy data for any sensitive data. It's too bad you didn't post your message a few
days earlier, because our monthly Access group meeting, which is attended by members of the
Access development team, is Tuesday evening. I might still have time to squeeze it in as a
question, or I might have to wait until the meeting in January. In any case, if you can send me
a copy, complete with all the API declarations you are using, I'm willing to take a look at it.
Right now, I don't have a quick answer for you.
Tom
_________________________________
Tom
Thanks for this - I attempted to paste just the relevant parts of my code
which were causing the problem in order not to make the post abnormally
long.
To answer the points you raised:-
1 - strFileName is selected and assigned correctly earlier in the full
procedure, hence the different passwords
2 - Option Explicit is included
3 - strTableName is selected and assigned correctly earlier in the full
procedure
4 - As the spreadsheets are password protected, by just using a Transfer
Spreadsheet without running an instance of Excel, I get an error - 3161 -
Could not decrypt file
Any further assistance greatly appreciated
Gary
_________________________________
Gary,
I forgot to include an example of using named arguments in my first reply.
Positional arguments:
DoCmd.TransferSpreadsheet , , strImportTableName, strFilename, True
Named arguments (better, in my opinion). Line continuation characters ( _) added to prevent word
wrap problems in this posted newsgroup message:
DoCmd.TransferSpreadsheet TransferType:=acImport, _
TableName:=strImportTableName, _
FileName:=strFilename, HasFieldNames:=True
Named arguments can be listed in any order desired. They help with readability of the code.
Tom
**************************************************
Hi Ken,
Here is the code listing that Gary attempted to post. I went ahead and added the line
continuation characters to four lines of code in his Select Case....End Select statement to
prevent word wrap in this posted message.
_________________
Private Sub btnLoadData_Click()
Dim xlApp2 As Object
Dim xlBook As Workbook
Dim xlSheet As Worksheet
Dim strFilename As String
Set strFilename = "c:\test.xls"
Set xlApp2 = CreateObject("Excel.Application")
xlApp2.Visible = True
Select Case Me.Data_File_From
Case "West Herts"
xlApp2.Workbooks.Open FileName:=strFilename, _
Password:="barbados"
Case "Luton"
xlApp2.Workbooks.Open FileName:=strFilename
Case "Stoke Mand"
xlApp2.Workbooks.Open FileName:=strFilename, _
Password:="amersham"
Case "South Bucks"
xlApp2.Workbooks.Open FileName:=strFilename, _
Password:="amersham"
Case "South Bucks Susp"
xlApp2.Workbooks.Open FileName:=strFilename, _
Password:="amersham"
End Select
DoCmd.TransferSpreadsheet , , strImportTableName, strFilename, True
xlApp2.Workbooks.Close
fCloseApp ("XLMain")
Set xlBook = Nothing
xlApp2.Quit
Set xlApp2 = Nothing
End Sub
Function fCloseApp(lpClassName As String) As Boolean
Dim lngRet As Long, hWnd As Long, pID As Long
hWnd = apiFindWindow(lpClassName, vbNullString)
If (hWnd) Then
lngRet = apiPostMessage(hWnd, WM_CLOSE, 0, ByVal 0&)
Call apiGetWindowThreadProcessId(hWnd, pID)
Call apiWaitForSingleObject(pID, INFINITE)
fCloseApp = Not (apiIsWindow(hWnd) = 0)
End If
End Function
____________________________
Gary,
1) The first error that I see, in the code shown above, is your use of the Set keyword for
initializing the string variable named strFilename:
Set strFilename = "c:\test.xls"
This should be:
strFilename = "c:\test.xls"
You are using strFileName in each Case statement of your Select Case...End Select construct. I'm
a little confused as to how you could have different passwords for the same strFilename....
2) Does your code include the statement:
Option Explicit
as the second line of code in the module? If not, please see this tip:
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions
Then, click on Debug > Compile DatabaseName. Correct any compile errors before attempting to run
your code.
3) Your line of code that reads:
DoCmd.TransferSpreadsheet , , strImportTableName, strFilename, True
references an undeclared variable, strImportTableName as the optional TableName argument. The
first optional parameter, TransferType, is not specified. Thus, it will default to acImport,
which appears to be what you want. I recommend including default parameters just so that your
code will be more readable. Also, I recommend using named arguments, instead of positional
arguments that you get by using commas only.
4) Your code appears to be A LOT more complicated than neccessary. If all you need to do is
import data from a given spreadsheet, you can use the following procedure. There is no need to
use automation to open the Excel file, or to use complicated API calls. I added an error handler:
Private Sub btnLoadData_Click()
On Error GoTo ProcError
Dim strFilename As String
Dim strImportTableName As String
strImportTableName = "tblImport"
strFilename = "c:\test.xls"
DoCmd.TransferSpreadsheet , , strImportTableName, strFilename, True
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in btnLoadData_Click event procedure..."
Resume ExitProc
End Sub
5) If your intent was to control which spreadsheet and / or table to import into, via the Select
Case...End Select construct, then you can assign values to the strFilename and / or
strImportTableName variables in this structure. One thing you left out, which you should
include, is a Case Else statement to handle unexpected values. Typically, you'll include a
message box in a Case Else to inform the user that an unknown error has occurred and to please
contact your friendly database administrator (and bring cookies & milk when you contact them with
problems!).
Good Luck,
Tom
___________________________________
Sorry... but the way your code pasted in the message is essentially
unreadable. Can you repost it so that we can see the line breaks that are in
your code? For example:
Private Sub btnLoadData_Click()
Dim xlApp2 As Object
Dim xlBook As Workbook
Dim etc... etc... etc.
--
Ken Snell
<MS ACCESS MVP>
___________________________________
I have a problem with the following code, which leaves an instance of Excel
visible in Task Manager. By a process of elimination I have got it down to
the fact that something in the DoCmd.Transfer Spreadsheet line is holding on
to an Excel reference somewhere - can anybody assist (if I comment this line
out, the instance is released, and not visible in Task Manager). Private Sub
btnLoadData_Click()Dim xlApp2 As ObjectDim xlBook As WorkbookDim xlSheet As
WorksheetDim strFilename as stringSet strfilename = "c:\test.xls"Set xlApp2
= CreateObject("Excel.Application")xlApp2.Visible = TrueSelect Case
Me.Data_File_From Case "West Herts" xlApp2.Workbooks.Open
FileName:=strFileName,
Password:="barbados" Case "Luton" xlApp2.Workbooks.Open
FileName:=strFileName Case "Stoke Mand"
xlApp2.Workbooks.Open FileName:=strFileName,
Password:="amersham" Case "South Bucks"
xlApp2.Workbooks.Open FileName:=strFileName,
Password:="amersham" Case "South Bucks Susp"
xlApp2.Workbooks.Open FileName:=strFileName,
Password:="amersham"End Select DoCmd.TransferSpreadsheet , ,
strImportTableName, strFileName, True xlApp2.Workbooks.ClosefCloseApp
("XLMain") Set xlBook = Nothing xlApp2.QuitSet xlApp2 =
Nothing End Sub Function fCloseApp(lpClassName As String) As Boolean Dim
lngRet As Long, hWnd As Long, pID As Long hWnd =
apiFindWindow(lpClassName, vbNullString) If (hWnd) Then lngRet =
apiPostMessage(hWnd, WM_CLOSE, 0, ByVal 0&) Call
apiGetWindowThreadProcessId(hWnd, pID) Call
apiWaitForSingleObject(pID, INFINITE) fCloseApp = Not
(apiIsWindow(hWnd) = 0) End IfEnd Function Thanks Gary
