Macros control on error?

[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.



It is possible that as part of the macro we may need to add a stored procedure step or run a command? I am at a loss on how to do this

 

[SkipVought]

hi,

Please post your code that opens and appends each workbook?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mart10]

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 ouput query to output desired format, results etc to a particular folder etc


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"));

 

[SkipVought]

Data creation is triggered by running a macro which tries to read each spreadsheet in turn via an individual table append query.

Is this an Access MACRO or is it a VBA procedure?

Please post your MACRO/VBA code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mart10]

Its MACRO. How do I get the macro code please, Its not obvious to me. I know how to see the SQL of individual queries but not the whole code of a macro?

 

[mart10]

Found how to do it! here it is as you can see only setup for 2 spreasdsheet links at the moment:

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

 

[mart10]

can anybody out there answer my question, not getting any real response so far,

thanks