Number of columns in a table

[greebo]

Probably a very simple question to the cognoscenti!

I am creating a new table (access 2003) by importing an excel spreadsheet, processing the data then deleting the table for the next import. The spreadsheets come in from worldwide users and shuld be in a standard format, issued centrally.

There should be 31 columns in the table but some users delete the final column heading as they do not populate it, then queries appending the data to a permanent table fall over - very annoying as the application triggers on scheduler and opens and closes automatically

is there a way to count the number of columns before I start the processing?

Any help would be appreciated!

 

[KenHigg]

You can open the spreadsheet from with Access - VBA using application objects and work with the spreadsheet just like you were in Excel - VBA. Then you could move from cell to cell and see if they had data in them.

 

[greebo]

Thanks for your quick reply

The problem is the application runs unattended. Users upload excel files via a website then access runs every 15 mins. to check for new files.

Approximately 130 files are processed over 2 days.

I have built in a huge amount of validation to ensure the critical columns are in the correct order, test the date fields and validate numeric fields, but I have not found a method of checking that this 31st column exists without VB throwing a wobbly when it doesn't!

 

[KenHigg]

You should be able to address that issue with the suggestion I made. One question is what do you want to do if the column is blank? Come to think of it, if it does not matter, say you don't really use the data, then why not ignore it all together?

 

[greebo]

Unfortunately some users complete the column(Title:Comments) and expect the information to be recorded and distributed. There are just a few users who decide to delete the column - despite detailed instructions on how to use the spreadsheets.

The spreadsheets cover upto 100,000 transactions monthly between global partners and the data is ultimately exported to excel and emailed to the counter parties.

Do you have any examples of code? I class myself as a "gifted amateur" having started with DataEase 20+ years ago and converted to access but I tend to be very conservative with my code.

Thanks
greebo

 

[KenHigg]

This opens the spreadsheet c:\book1.xls:

    Dim appexcel As Object
    Set appexcel = CreateObject("Excel.Application")
    appexcel.Workbooks.Open "c:\book1.xls"
    appexcel.Sheets("Sheet1").Select
    appexcel.Rows("1").Font.Bold = True

And set the 1 row font to bold. You have to set you excel refs on.

 

[gemma-the-husky]

after you import the table, check that all the expected fields are present. if not reject the import and inform the user.

basically test the fields by

len(currentdb.tabledefs("tblname").fields("expectedfield").name) > 0

you could add any missing columns automatically with code if you wanted.

 

[KenHigg]

I was thinking it's a little more complex than that Dave. Sound like if the column is empty he still want to bring in the remaining columns. I have a similar situation where I open the spreadsheet and look at each cell in each row and do a test. If it passes each test then I append it to a dao recordset data field.

 

[gemma-the-husky]

ken, what i meant was:

there are generally two options with importing

either import the data, ignoring any column heads. In which case you have to be certain the source data was properly laid out, and the columns are in the right order, as you cant check/verify this.

or

import the data with column heads. in which case the column order is not significant. If you import this way then if it is critical to verify that the correct file was indeed imported, then you have to test that you have all the required columns. If any columns (fields) are missing, then you either need to reject the import, or create a blank column.

--------
if you link to the data, the same principle applies - you need to find a way to verify the data before you use it.

In general, iy isnt easy to give any feedback with an automatic process.

---------

This is one reason why I hate users to import spreadsheets - its far better that you import the source csv file if there is one, than allow users to open the csv in excel - in some cases excel changes things anyway, the users may edit the data (as in this case) and importing excel is less reliable than importing a csv file anyway.

 

[greebo]

I really appreciate both your help!

Unfortunately the length test gives me "Item not found in this collection" which halts the code.

Using the application objects, can I set field AE3 to "Comments" then save and close the file? Sorry to be a pain, but my knowledge of Excel VBA is very basic and my advisor for Excel is on holiday!

In the past the files were submitted by email and processed manually but shortening deadlines have required web submission and automatic validation. I do reject files with invalid data and send email reports, but this one had me stumped.

Another problem I had was changes of the tab name in Excel, but my advisor gave me 'OnSave' code to reset the tab name - though this assumes the users will be allowed to open excel files which contain code! I assume this would also be possible to manage through access.

Yours most gratefully

greebo

 

[gemma-the-husky]

sorry grrebo

yes the length WILL fail on missing fields - you need to error trap it

dim fieldfound as boolean

on error resume next
fieldfound = len(currentdb.tabledefs("tblname").fields("expecte dfield").name) >

i've just edited this - this will return true for found, and should return false for not found, although it actually generates a handled runtime error - so I'm not 100% sure that it does return false for not found

 

[greebo]

Ken & Dave,

In the past, the report data was replicated on an import sheet and each column was prefixed with an alpha character to ensure that all data imported as text - avoiding the usual problem of mixed alpha/numeric data in fields. This also ensured that I had a least an alpha character in every field in the "Comments" column.

By creating the import table anew for each import I avoid the usual problems with importing alpha/numeric data from excel.

I wish I had joined this forum years ago

greebo (named after a cat)

 

[KenHigg]

Allow me to digress for a moment; If you allow your users to monkey with the spreadsheet template and potentially change what ever they wish you are never going to be able to automate an import routine.

a. If they can monkey with it then you should move from cell to cell validating the contents and when it passes then append the cell content to the appropriate field in the table. If at any time it fails then reject the spreadsheet and either import it manually or kick it back to the user.

b. Send them a template that is locked to the point where they can't change worksheet names, columns etc... Then when you get these back your automated import routine should be a lot less cumbersome...

 

[Dennisk]

I've had a similar problem so I parsed the text file before importing it counting all the columns.

So you could do something similar and if the last column was missing, read the text file again and add the column with null data.

 

[greebo]

Ken,

Unfortunately I am not allowed to lock down the template. Some users manually enter 50 - 100 lines per month but most use feeder systems to download anything from 5,000 to 20,000 lines and paste these onto the sheet. The manual process has worked effectively for over 2 years, but needed intervention. I'm nearly there with the automatic process.

Hopefully if the users keep getting rejection reports they may learn to submit the data properly in the first place. I just need to avoid the code halting when it hits a problem I haven't covered

greebo

 

[KenHigg]

It appears we are kind of going around in circles here...

In my experience the most robust process is going to be open the spreadsheet and examine each cell separately and append cell by cell, field by field into a temp table. If the entire spreadsheet is appended then move the data into the primary table, etc. If any of it fails then execute a failure routine that clears out the temp table and notifies the user of the failure, etc...

Other wise (whew...)

When you attempt to attach to the spreadsheet and a column is missing what is the exact error you are getting?

 

[editolis]

Try this my friend:
=======================================================
Function FieldExists() As Boolean
Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim strName As String
Dim strSQL

Set db = CurrentDb
Set tbl = db.TableDefs("YOUR_TABLE")
strName = "Comments"
For Each fld In tbl.Fields
If fld.Name = strName Then
FieldExists = True
Exit For
End If
Next
If FieldExists Then
MsgBox "Field Name Exists"
Else
MsgBox "Field Name Does Not Exist"
strSQL = "ALTER TABLE YOUR_TABLE ADD COLUMN Comments MEMO"
CurrentDb.Execute strSQL, dbFailOnError

End If
db.Close

End Function

=====================================================
Then create a button and put this code:

Private Sub YourButton_Click()
FieldExists
End Sub

 

[greebo]

Now working - Thanks to everyone for your help!

Editolis - my solution is based on your code. I'm very grateful as I can add the column and not have to reject the entire submission.

Dave - your code will enable me to validate and reject where critical columns have been deleted.

Ken - Is it possible to use this within the example code you gave me? I am going to investigate opening excel files from within access but now I can take a little more time to do it.
========================

Dim Wks As Worksheet
With ThisWorkbook
For Each Wks In .Worksheets
If Trim(UCase(Wks.CodeName)) = "MONTH" Then
Wks.Name = "Month"
End If
Next
End With
========================


Also will this work to save and close when done?
appExcel.Workbooks.Save
appExcel.Workbooks.Close

Thanks again for all your help

greebo​

 

[editolis]

Glad to help.

 

[KenHigg]

While Editolis will solve for that column error you will have to potentially code for every such error specifically using that methodology. i.e. Is column 6 named 'abc', is column 5 named 'def', is does column 1 start in column 1 of the spreadsheet, etc., etc., etc... That's going to be a bit un-wielding after a point.

Yes you need to close stuff, I forgot to put that part in. As for your code, something like what you have should work...