Importing Issue - Read Only option? (1 Viewer)

[Nevsky78]

Hi all,

I have an import procedure which pulls all spreadsheets with a certain tab name from multiple files within a specified folder into one 'master' table in Access.

Whenever one of the 'source' files is open, the VBA code cannot run. My questions is, does anyone know of any code I might be able to insert in my procedure that will pull the spreadsheet tab in even when the file is open?

Public Function ImportAllContractPrices()

On Error GoTo Err_F
DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, "Listings Full"
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
blnHasFieldNames = True
strPath = "F:\2011 Information\2011 Core Reports\2011 SALES\2011 Listings\"
strTable = "Listings Full"
strFile = Dir(strPath & "*.xlsx")
Do While Len(strFile) > 0
      strPathFile = strPath & strFile
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
            strTable, strPathFile, blnHasFieldNames, "Listing!"
'Kill strPathFile
strFile = Dir()
Loop
Exit_F:
Exit Function
Err_F:
MsgBox Err.Number & " " & Err.Description
Resume Exit_F
DoCmd.SetWarnings True
End Function

Thanks,
Nick

 

[gemma-the-husky]

i dont think you can. you will get a trappable rte. maybe warn the user, and give him a retry option.

 

[Nevsky78]

Hi Dave,

Well, this is part of a macro that is run by a batch file overnight. I've asked users to always save and close the relevant files to no avail.

I'll probably run another batch file closing down any files that are open within the folder. Not ideal as the files are always changing.

Nick