controling of macro which is expecting Excel linked sheets

[mart10]

I am using Access 2003

We have an ACCESS database that is potentially LINKED to 1 to n Excel files (potentially up to 18) in a particualr folder that will be cleared down each evening and then loaded up with Excel sheets each morning. Data creation is triggered by running a macro which tries to read each spreadsheet in turn via an individual table append query.We have a problem when one of the Excel files is missing as the macro is expecting all of them. In reality this may not happen but if it does what happens then is that the process just stops at the point where the missing file is and doesn't move on to the following file. What we need to do really is to check if the expected file is there and process it if it is there or move on to the next one if not.


This is the macro code:

Option Compare Database

'------------------------------------------------------------
' m_CashForecast
'
'------------------------------------------------------------
Function m_CashForecast()
On Error GoTo m_CashForecast_Err

' Capita Group Treasury - martyn fenton 25/01/2011
Beep
MsgBox "Prepare Cash Forecast Table", vbInformation, "Cash Forecast Table"
DoCmd.SetWarnings False
' Deletes CashForecast table before appending all template data
DoCmd.OpenQuery "qry_Delete_CashForecast", acViewNormal, acEdit
' Append Data1 to Cash Forecast table
DoCmd.OpenQuery "qry_DataCF1", acViewNormal, acEdit
' Append Data2 to Cash Forecast table
DoCmd.OpenQuery "qry_DataCF2", acViewNormal, acEdit
DoCmd.Close acTable, "Forecast Data"
Beep
MsgBox "Cash Forecast Table prepared, will now create file for IT2 which must then be saved as a CSV file, please wait a couple of minutes", vbInformation, "Cash Forecast IT2 File"
DoCmd.OutputTo acTable, "Cash Forecast Data", "MicrosoftExcelBiff8(*.xls)", "V:\TMS Project\implementation\Interfaces\Cash forecasting\ExcelCashFlow.xls", True, "", 0


m_CashForecast_Exit:
Exit Function

m_CashForecast_Err:
MsgBox Error$
Resume m_CashForecast_Exit

End Function


The first query clears a table [Balance Data], I then have 18 very similar queries one after the other (As below). The only difference is the source data table name (Excel - DATA1, DATA2 etc etc) After the final 18th query I then create an output query to output desired format, results etc to a particular folder etc
The example macro and code is just looking at 2 append queries at the moment


INSERT INTO [Balance Data] ( AccountNo, Ccy1, TransactionAmount, ValueDate1, PostedDate, [User Ref], [Reported Ref], TransactionComment, ClosingAvailable, ClosingLedger )
SELECT DATA1.AccountNo, DATA1.Ccy1, DATA1.TransactionAmount, DATA1.ValueDate1, DATA1.PostedDate, DATA1.[User Ref], DATA1.[Reported Ref], DATA1.TransactionComment, DATA1.ClosingAvailable, DATA1.ClosingLedger
FROM DATA1
WHERE (((DATA1.AccountNo)<>"0") AND ((DATA1.ValueDate1)=Date()) AND ((DATA1.ItemType)="A"));

 

[lameid]

Rather than link the data why not just delete the file and export the data you are appending now?


Having said that, have you considered using DIR() to test if the file exists?

 

[mart10]

Dont realy want users to have anything to do with file handling other than just placing files in an empty folder and so using links. The users will be aware if a file is missing say 17 out of 18 present. But the issue is getting the macro to be aware and ignore any missing files - I need this to be automated. My only other option would be allways to leave dummy empty copies of a file in the folder but I dont like that idea at all

 

[lameid]

Having said that, have you considered using DIR() to test if the file exists?

 

[PHV]

A crude way is to replace this:
On Error GoTo m_CashForecast_Err
with this:
On Error Resume Next

OR

this:
Resume m_CashForecast_Exit
with this:
Resume Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886