Solved Importing Excel Spreadsheet that has a password

[Number11]

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]

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]

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

 

[CJ_London]

plenty of examples on the web - google something like 'vba code to open a password protected excel file'

here is an example link http://www.vbaexpress.com/forum/showthread.php?2634-Solved-auto-open-password-protected-excel-file

 

[Number11]

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?

 

[arnelgp]

there is somewhat similar solution found here: https://www.experts-exchange.com/qu...adsheet-with-password-protected-workbook.html

 

[Number11]

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

 

[CJ_London]

looks like a good solution

 

[Number11]

So i am now wanting to to this code the option to remove any filters that may be present - any thoughts

 

[Gasman]

Record a macro in excel to set filters and then remove them.
Amend that code to suit.

 

[Isaac]

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]

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]

'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]

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.