Tansferring from Excel to Access- what was done, what wasn't

[bugsy]

Hi

I am transferring 10 spreadsheets from Excel to Access when button on a form is clicked. (using TransferSpreadseet)

Depending on the day entered on a form different folders are selected.
There are ten tables - 1,2,3,4,5,6,7,8,9,10

question --
If spreadsheet doesn't exist for a given day, I would like it to be ignored, and go forward.

If spreadsheet exists, but it's in a wrong format, i would like to get message like "spreadsheet 3.xls is incorrect format. Please check it"

how can I condition this ?

 

[bugsy]

this is the way i am importing --

'Maxcor
pathMXR = Path & "\maxcor.xls"
DoCmd.TransferSpreadsheet acImport, , "Maxcor", pathMXR, vbYes

'Phoenix
pathPHX = Path & "\Phoenix.xls"
DoCmd.TransferSpreadsheet acImport, , "Phoenix", pathPHX, vbYes

'1
pathRAFF = Path & "\1.xls"
DoCmd.TransferSpreadsheet acImport, , "1", pathRAFF, vbYes

'C
pathbgc = Path & "\C.xls"
DoCmd.TransferSpreadsheet acImport, , "C", pathbgc, vbYes

'G
pathbgc = Path & "\G.xls"
DoCmd.TransferSpreadsheet acImport, , "G", pathbgc, vbYes

 

[The_Doc_Man]

Look into "OnError" type traps and reset the OnError before each import.
Set a state variable to 0.
If you take the trap, set the variable to the import number and resume.
After the DoCmd action, test the variable. If it is zero, everything worked.

If not, you have issues to address. This tells you a table failed to import.

If you need more detail than this, though, you might have a bit of a problem. For instance, I'm not sure what you get in the OnError trap when all but one of the spreadsheet rows imports OK. In other words, partial success. Best and simplest answer: Try it yourself and see.

 

[bugsy]

How to reset OnError ?
Can i have a separate on OnError for each statement ?

'1
pathbgc = Path & "\1.xls"
DoCmd.TransferSpreadsheet acImport, , "1", pathbgc, vbYes


'2
pathMXR = Path & "\2.xls"
DoCmd.TransferSpreadsheet acImport, , "2", pathMXR, vbYes

'3pathPHX = Path & "\3.xls"
DoCmd.TransferSpreadsheet acImport, , "3", pathPHX, vbYes

 

[Dennisk]

In my experience you will not get an error in code. What will happen though is a table will be created called ImportError1....2...etc.
That will list the row(s) in question.

So drop all the error tables prior to importing then test if an error table exists. This works OK as I developed it myself a few months ago, although I think it is now scrapped.

 

[DCrake]

Simple Software Solutions

Bugsy

On the assumption that the layout of each spreadsheet is the same no matter what the source is, ie all the columns are the same and in the same position, only the data is different, you could trap the invalid format error using the following code.

First create a function as per below


Function IsValidSpreadsheet(FolderName As String, SpreadsheetName As String, ValidationRule as String) As Boolean

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim bFlag As Boolean

bFlag True

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(FolderPath & "\" & SpreadsheetName)

Set xlSheet = xlBook.Worksheets(1)
If xlSheet.Range("A1") <> ValidationRule Then
bFlag = False
End If

xlApp.Quit

IsValidSpreadsheet = bFlag

End Function


Next On your button on the form place the command
Dim MyPath As String
Dim MyFile As String
Dim MyCriteria As String

MyPath = "C:\Test"
MyFile = "Spreadsheet 1.xls"
MyCriteria = "Miami"

If IsValidSpreadsheet(MyPath,MyFile,MyCriteria) = False Then

MsgBox "The spreadsheet " & MyPath & "\" & MyFile & " contains invalid or unknown data for this source",vbExclamation+vbOkOnly,"Transfer Spreadsheet Failed"​

Else

TransferSpreadsheet ....​

End If

The MyVariables will change to suit each spreadsheet in turn

The criteria can be defined by yourself and does not have to reside in A1 it could be anywhere on any sheet. Thats up to you as the programmer to decide on what validation to employ.

Summary:
The location, filename and validation text is passed to a function
The function opens an instance of Excel and reads the contents of a specified worksheet and cell to compare against the validation test passed to the function.
Closes the instance of Excel

If there is a difference then it returns False and informs the user via a message box,otherwise it runs the TransferSpreadsheet command line.

This is repeated for each intended spreadsheet

Code Master:

 

[Lightwave]

D - you should be able to delete your posts by hitting edit and then delete... (only works for your own posts)