Excel Import Not working 1

[SysDupe123]

I'm trying to import a table from an Excel file. The problem is that the file is shared and I get an error saying it cannot decrypt the file. I tried to copy the file and import, but the shared status remains.
I then tried to just copy the worksheet I needed into a file and then import. That worked.
I'm trying to see if I can set that up programmatically so it is automated but I'm having a lot of trouble.

Now, I'm gettting Copy method of Worksheet class failed as an error and I don't know where to go from here.

Is there a way to be able to import the worksheet that I'm missing, maybe?
The file is an old file being used by another department that has been copying it forever, so they don't know the password to unshare it, so I cannot use that to get access to the data.

 

[PHV]

Any chance you could post your actual code raising the error ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SysDupe123]

The original error (decrypt) was just from a DoCmd.TransferSpreadsheet

The copy error was from this:

Set fs = New FileSystemObject

MatrixFile = GetOpenFile(DtFldr, "Select Matrix Location") [green]' this function allows selection of file.[/green]

FlNameBase = fs.GetBaseName(MatrixFile)
NwMatrixFile = "G:\" & FlNameBase & ".xls"

Set XLbase = New Excel.Application
Set XLNew = New Excel.Application
Set XLNwSt = XLNew.Workbooks.Add
Set XLSheet = XLbase.Workbooks.Open(MatrixFile)
XLbase.ActiveWorkbook.Sheets("Matrix").Select
XLbase.ActiveWorkbook.Sheets("Matrix").Activate
XLbase.ActiveSheet.Copy XLNwSt.Worksheets("Sheet1") [red]'The Error happens here![/red]
XLSheet.Close
XLNwSt.SaveAs NwMatrixFile

 

[PHV]

And what about this ?
Set XL = New Excel.Application
Set WN = XL.Workbooks.Add
Set WB = XL.Workbooks.Open(MatrixFile)
WB.ActiveSheet.UsedRange.Copy WN.ActiveSheet.Range("A1")
WB.Close False
Set WB = Nothing
WN.SaveAs NwMatrixFile

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SysDupe123]

Thanks! Worked great! You deserve a star!