DoCmd.TransferSpreadsheet Error Trapping

[jameshall]

Hi All.

I am using DoCmd.TransferSpreadsheet to import some data from Excel. This is a weekly task and is completed by different people. Every so often the workbook gets renamed and this confuses my TransferSpreadSheet command

Is there away to read or update the names of worksheets in my Spreadsheet before I import the data?

Hopefully this will resolve the changed worksheet names.

Thanks In Advance

James

 

[MakeItSo]

Hi James.

You could try using FileSearch:

With Application.Filesearch
.LookIn = your Folder
.Filename = your Filename
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) = 0 Then
[blue] For Each ff in .FoundFiles
...
Next [/blue]
End If
End With

By putting a recognition criteria in the for loop marked blue, you could locate the correct file...

Greetings,
Andy

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de