I am in the process of writing module using vba in ACCESS 2003 which imports
forecasted financial data from a particular excel file based on three inputs.
I am trying to include an "error check" that will end the process if the
three inputs do not match specific data in the excel file. I'm not sure how
to go about doing this. Do you have any ideas what the best way to do this
would be? Below is the code I have for the import part so far if that is
helpful.

Sub Test1()

Dim ImportDir As String
Dim Forecast As String
Dim Version As String
Dim Country As String
Dim Path As String

ImportDir = "I:\MED\VisionFund International\Reports - MED misc\MFI
Forecasts\"
Forecast = InputBox("Enter Forecast Period")
Version = InputBox("Enter Version")
Country = InputBox("Enter Country")
Path = ImportDir + "MFI Forecasts " + Forecast + "\Version " + Version + "\"
+ Country + " Forecast " + Forecast + ".xls"

Select Case Forecast

Case ""

Case Else

DoCmd.TransferSpreadsheet acImport, 8, "Source Data", Path, True, "Data"

DoCmd.RunMacro "Build Global", 1

End Select

End Sub

Its easiest to just import the data and then examine it after importing to
see if its correct, you can always delete the records if it is not correct.
You could always open an Excel object from VBA and read it in Excel but that
will be quite complex.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"Access Tinkerer" wrote:

> I am in the process of writing module using vba in ACCESS 2003 which imports
> forecasted financial data from a particular excel file based on three inputs.
> I am trying to include an "error check" that will end the process if the
> three inputs do not match specific data in the excel file. I'm not sure how
> to go about doing this. Do you have any ideas what the best way to do this
> would be? Below is the code I have for the import part so far if that is
> helpful.
>
> Sub Test1()
>
> Dim ImportDir As String
> Dim Forecast As String
> Dim Version As String
> Dim Country As String
> Dim Path As String
>
> ImportDir = "I:\MED\VisionFund International\Reports - MED misc\MFI
> Forecasts\"
> Forecast = InputBox("Enter Forecast Period")
> Version = InputBox("Enter Version")
> Country = InputBox("Enter Country")
> Path = ImportDir + "MFI Forecasts " + Forecast + "\Version " + Version + "\"
> + Country + " Forecast " + Forecast + ".xls"
>
> Select Case Forecast
>
> Case ""
>
> Case Else
>
> DoCmd.TransferSpreadsheet acImport, 8, "Source Data", Path, True, "Data"
>
> DoCmd.RunMacro "Build Global", 1
>
> End Select
>
> End Sub
>

Could I create a similar conditional clause which compares the three values
in ACCESS tables and then ends the process if they do not match?

"Dorian" wrote:

> Its easiest to just import the data and then examine it after importing to
> see if its correct, you can always delete the records if it is not correct.
> You could always open an Excel object from VBA and read it in Excel but that
> will be quite complex.
> -- Dorian
> "Give someone a fish and they eat for a day; teach someone to fish and they
> eat for a lifetime".
>
>
> "Access Tinkerer" wrote:
>
> > I am in the process of writing module using vba in ACCESS 2003 which imports
> > forecasted financial data from a particular excel file based on three inputs.
> > I am trying to include an "error check" that will end the process if the
> > three inputs do not match specific data in the excel file. I'm not sure how
> > to go about doing this. Do you have any ideas what the best way to do this
> > would be? Below is the code I have for the import part so far if that is
> > helpful.
> >
> > Sub Test1()
> >
> > Dim ImportDir As String
> > Dim Forecast As String
> > Dim Version As String
> > Dim Country As String
> > Dim Path As String
> >
> > ImportDir = "I:\MED\VisionFund International\Reports - MED misc\MFI
> > Forecasts\"
> > Forecast = InputBox("Enter Forecast Period")
> > Version = InputBox("Enter Version")
> > Country = InputBox("Enter Country")
> > Path = ImportDir + "MFI Forecasts " + Forecast + "\Version " + Version + "\"
> > + Country + " Forecast " + Forecast + ".xls"
> >
> > Select Case Forecast
> >
> > Case ""
> >
> > Case Else
> >
> > DoCmd.TransferSpreadsheet acImport, 8, "Source Data", Path, True, "Data"
> >
> > DoCmd.RunMacro "Build Global", 1
> >
> > End Select
> >
> > End Sub
> >