Solved Importing Excel Spreadsheet that has a password (1 Viewer)

Number11

Member
Local time
Today, 21:16
Joined
Jan 29, 2020
Messages
228
So I am looking for VBA code to allow me to select a file using filedialog

Dim dlg As FileDialog
Set dlg = Application.FileDialog(msoFileDialogFilePicker)

With dlg
.Title = "Select the Excel file to import"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel Files", "*.xlsx", 1
.Filters.Add "All Files", "*.*", 2
.InitialFileName = "c:\"

If .Show = -1 Then
strFileName = .SelectedItems(1)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Test", strFileName, True, "B!"

Due to a password being set on this spreadsheet i get run time error "run-time error '3274' external table is not in the expected format"

how can I workaround this issue :)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:16
Joined
Feb 19, 2013
Messages
12,795
simply you can't use this method connect to a password protected excel file because the file itself is encrpyted - compare an encrypted one with an identical unencrypted one and you will see the encrypted one has a larger file size.

The only way would be to use automation to open the file, providing the password at the same time. Once open you can have code to do the updates
 

Number11

Member
Local time
Today, 21:16
Joined
Jan 29, 2020
Messages
228
simply you can't use this method connect to a password protected excel file because the file itself is encrpyted - compare an encrypted one with an identical unencrypted one and you will see the encrypted one has a larger file size.

The only way would be to use automation to open the file, providing the password at the same time. Once open you can have code to do the updates
Ok thats sound a workable solution and idea on how the code would look :)
 

Number11

Member
Local time
Today, 21:16
Joined
Jan 29, 2020
Messages
228
really looking for the option to open FileDialogFilePicker and then open that excel with password and keep open whilst it imports and then close the excel afterwards any ideas?
 

Number11

Member
Local time
Today, 21:16
Joined
Jan 29, 2020
Messages
228
I worked it out and this is working ..

Dim oExcel As Excel.Application
Dim oWb As Excel.Workbook
Set oExcel = CreateObject("Excel.Application")
Dim dlg As FileDialog
Set dlg = Application.FileDialog(msoFileDialogFilePicker)

With dlg
.Title = "Select the Excel file to import"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel Files", "*.xlsx", 1
.Filters.Add "All Files", "*.*", 2
.InitialFileName = "c:\"

If .Show = -1 Then
strFileName = .SelectedItems(1)
strPassword = "test"

'Open protected workbook and remove password
Set oWb = oExcel.Workbooks.Open(FileName:=strFileName, Password:=strPassword)
oWb.Password = ""
oWb.Close SaveChanges:=True


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Test", strFileName, True, "B!"

'Restore password
Set oWb = oExcel.Workbooks.Open(FileName:=strFileName)
oWb.Password = strPassword
oWb.Close SaveChanges:=True
oExcel.Quit
Set oExcel = Nothing

End If
End With
End Sub
 
Last edited:

Users who are viewing this thread

Top Bottom