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

Number11

Member
Local time
Today, 10:55
Joined
Jan 29, 2020
Messages
607
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, 10:55
Joined
Feb 19, 2013
Messages
16,553
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, 10:55
Joined
Jan 29, 2020
Messages
607
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, 10:55
Joined
Jan 29, 2020
Messages
607
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, 10:55
Joined
Jan 29, 2020
Messages
607
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:

Number11

Member
Local time
Today, 10:55
Joined
Jan 29, 2020
Messages
607
So i am now wanting to to this code the option to remove any filters that may be present - any thoughts
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:55
Joined
Sep 21, 2011
Messages
14,038
Record a macro in excel to set filters and then remove them.
Amend that code to suit.
 

Isaac

Lifelong Learner
Local time
Today, 03:55
Joined
Mar 14, 2017
Messages
8,738
So i am now wanting to to this code the option to remove any filters that may be present - any thoughts
1) make a new post for every question
2) that, in Excel forum

check out Worksheet.AutoFilter
 

Number11

Member
Local time
Today, 10:55
Joined
Jan 29, 2020
Messages
607
1) make a new post for every question
2) that, in Excel forum

check out Worksheet.AutoFilter
No need to create a new post as its related to my code that removes the password and its not excel i am looking at its access, :)
 

ebs17

Well-known member
Local time
Today, 11:55
Joined
Feb 7, 2020
Messages
1,879
Code:
'Open protected workbook and remove password
Set oWb = oExcel.Workbooks.Open(FileName:=strFileName, Password:=strPassword)
What is that other than code on Excel objects and thus a pure Excel topic? If I pick up a banana in Germany, I'm not immediately responsible for banana cultivation in Guatemala.

No, the responsibility of Access-VBA ends with the creation of the reference to the workbook (oWB), the internal processes in the workbook are an Excel topic.
 

Isaac

Lifelong Learner
Local time
Today, 03:55
Joined
Mar 14, 2017
Messages
8,738
No need to create a new post as its related to my code that removes the password and its not excel i am looking at its access, :)

No, that's Excel VBA. Doesn't matter that you're doing it from Access, Outlook, Powerpoint, Word or Visio. It's still Excel VBA.
Thus, you're more likely to get help in the Excel forum, because the people most experienced with Excel vba will be looking there.

Suit yourself - just some advice.
 

Users who are viewing this thread

Top Bottom