1. Forum
  2. USENET
  3. comp.databases.ms-sqlserv

  • Import spreadsheet

    From Keith Tizzard@internet.shopping@foobox.com to comp.databases.ms-access on Mon Nov 2 05:55:52 2020
    From Newsgroup: comp.databases.ms-sqlserv

    I know how to import a spreadsheet with
    DoCmd.TransferSpreadsheet

    However I have one that is password protected. Is there a way of importing it into Access using VBA by providing the password?

    An alternative would be to create a link to it. I would still need a way of providing the password.

    Jim
    --- Synchronet 3.21d-Linux NewsLink 1.2
  • From Keith Tizzard@internet.shopping@foobox.com to comp.databases.ms-access on Mon Nov 2 08:24:59 2020
    From Newsgroup: comp.databases.ms-sqlserv

    Should have googled first !! Found it:

    Public Sub ImportProtected(strFile As String, strPassword As String)
    Dim oExcel As Object, oWb As Object
    Set oExcel = CreateObject("Excel.Application")
    Set oWb = oExcel.Workbooks.Open(FileName:=strFile, Password:=strPassword) DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Import", strFile, -1
    oWb.Close SaveChanges:=False
    oExcel.Quit
    Set oExcel = Nothing
    End Sub

    Jim


    On Monday, 2 November 2020 at 13:55:58 UTC, Keith Tizzard wrote:
    I know how to import a spreadsheet with
    DoCmd.TransferSpreadsheet

    However I have one that is password protected. Is there a way of importing it into Access using VBA by providing the password?

    An alternative would be to create a link to it. I would still need a way of providing the password.

    Jim
    --- Synchronet 3.21d-Linux NewsLink 1.2