iT邦幫忙

0

ACCESS 查詢資料VBA轉有密碼EXCEL檔的問題

請問各位大大,以下VBA轉沒有密碼excel時正常,
但excel加密碼後,轉出就會有錯,可以幫忙看一下錯在何處嗎?
謝謝!

Function 轉出資料到excel()
On Error GoTo 轉出資料到excel_Err
Dim exApp As Object
Dim exWB As Object
Dim strPassword As String
Dim strFilePath As String

strPassword = "1234"
strFilePath = "D:\員工.xls"
Set exApp = CreateObject("Excel.Application")
Set exWB = exApp.Workbooks.Open(fileName:=strFilePath, Password:=strPassword)
If (MsgBox("確定要轉到excel嗎?", 1) = 1) Then
DoCmd.TransferSpreadsheet acExport, 8, "查詢員工", strFilePath, True, ""
Beep
MsgBox "轉excel成功!", vbInformation, ""
Else
Beep
MsgBox "轉excel失敗!", vbInformation, ""
End If

exWB.Close SaveChanges:=False
exApp.Quit

Set exWB = Nothing
Set exApp = Nothing

轉出資料到excel_Exit:
Exit Function

轉出資料到excel_Err:
MsgBox Error$
Resume 轉出資料到excel_Exit

End Function

1 個回答

4
liurambo0911
iT邦高手 1 級 ‧ 2012-06-25 22:39:16
最佳解答

utopia提到:
Set exWB = exApp.Workbooks.Open(fileName:=strFilePath, Password:=strPassword)

在這一行之後 先試試看能不能正確讀取到資料

我猜是根本搞錯密碼

我要發表回答

立即登入回答