Any way to validate spreadsheet headers before import?

[google]

Is there anyway to programatically confirm that specific field names
exist in the first row of a given Excel spreadsheet from code within
Access? I have code set up to import data from our spreadsheets from
HQ into our Access database for further use, using the first row as
column headings to determine the destination field in the Access table.
Since it's possible (and probable) that HQ could change the format of
the spreadsheets without my knowledge, I'd like to be able to confirm
that the needed fields are present (and named properly) before import.
I'm not very familiar with examining Excel data from within Access.
Any suggestions on how to do that?

Thanks!

 

[Guest]

I'm not sure if this is the best method but you can search for the value you
want, then capture the row and column. Here is a code snippet from a module
I use. NOTE, I pass the Excel object to the function where you may need to
explicitly declare the object and then set it:

Function fFormat(strLocation As String, objExcel As Excel.Application, objWB
As Excel.Workbook, objWS As Excel.Worksheet)

Dim intUserBookmark As Integer
Dim intGroupBookmark As Integer
Dim intGroupDeleteBookmark As Integer
Dim intTotalsBookmark As Integer
Dim intTotalsDeleteBookmark As Integer
Dim intDateBookmark As Integer
Dim strDateValue As String
Dim objTemp As Object

'Format the File SAN Volume Reports based on location
'
Select Case strLocation
Case "Austin"
With objWS.Range("A1:A50")
'Find the bookmark for user volumes
'
Set objTemp = .Find("User Volumes", LookIn:=xlValues)
objTemp.Select
intUserBookmark = ActiveCell.Row

'Find the bookmark for group volumes
'
Set objTemp = .Find("Group Volumes", LookIn:=xlValues)
objTemp.Select
intGroupBookmark = ActiveCell.Row
intGroupDeleteBookmark = intGroupBookmark - 1

'Find the bookmark for the totals
'
Set objTemp = .Find("Totals", LookIn:=xlValues)
objTemp.Select
intTotalsBookmark = ActiveCell.Row
intTotalsDeleteBookmark = intTotalsBookmark - 1

'Find the bookmark for the report date
'
Set objTemp = .Find("Run*", LookIn:=xlValues)
objTemp.Select
intDateBookmark = ActiveCell.Row

'Capture the date the report was generated
'
strDateValue = Trim(Mid(objTemp, 15, 10))

End With

....rest of code...

 

[Guest]

Here is some code that will help understand how to open an Excel workbook as
an object. Create a new standard modeule and paste this part in it:

'***************Start of modExcelRoutines ***************
Option Compare Database
Option Explicit

' Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long

Sub GetExcel()
Dim MyXL As Object ' Variable to hold reference
' to Microsoft Excel.
Dim ExcelWasNotRunning As Boolean ' Flag for final release.

' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument returns a
' reference to an instance of the application. If the application isn't
' running, an error occurs.
Set MyXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error occurred.

' Check for Microsoft Excel. If Microsoft Excel is running,
' enter it into the Running Object table.
DetectExcel

' Set the object variable to reference the file you want to see.
Set MyXL = GetObject("c:\vb4\MYTEST.XLS")

' Show Microsoft Excel through its Application property. Then
' show the actual window containing the file using the Windows
' collection of the MyXL object reference.
MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True
' Do manipulations of your file here.
' ...
' If this copy of Microsoft Excel was not running when you
' started, close it using the Application property's Quit method.
' Note that when you try to quit Microsoft Excel, the
' title bar blinks and a message is displayed asking if you
' want to save any loaded files.
If ExcelWasNotRunning = True Then
MyXL.Application.Quit
End If

Set MyXL = Nothing ' Release reference to the
' application and spreadsheet.
End Sub

Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Sub
'***************End of modExcelRoutines******************

Here is an example of how to set up object references to Excel. This is
important. If you do it incorrectly, Access will get really stupid and
create additional instances of Excel, so when you are done, the will be Excel
Processes running you are not aware of and will cause a hang up if you try to
open an excel file. You can determine if it is working correctly be checking
the processes tab in task manager. The code below references the code above.

Create an instance of Excel
On Error Resume Next ' Defer error trapping.
Me.txtStatus = "Opening Spreadsheet"
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo LoadAdjustedActuals_Err
'This I do for performance and so the user can't see what is gong on
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False

'Open the Workbook
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)
'Select the Sheet you want to work with
Set xlSheet = xlBook.Worksheets("Actuals_res_export")

Now, lets loop through first row checking field names. I will assume a
reference to a table established as rstCheck

Dim lngFldCtr as Long
Dim flds as Fields
Dim fld as Field
Dim blnOk as Boolean

Set flds = rstCheck.Fields

For lngFldCtr = 1 to 30 'Assumes 30 fields in the spreadsheet.
blnOk = False
For Each fld In flds
If fld.Name = xlSheet.Cells(1, lngFldCtr).Value Then
blnOk = True
Exit For
End If
Next fld
If Not blnOk Then
MsgBox "Field " & xlSheet.Cells(1, lngFldCtr).Value _
& " Is Not in the Table"
Exit For
End If
Next lngFldCtr

If blnOk Then
MsgBox "All's Well That Ends Well"
End If

The above does not compare to see if there are the same number of fields.

Hope this is usefull.

 

[Ron2005]

one way:

From within Access:

Open the spreadsheet
1) go to the desired worksheet (if they haven't changed the name on
you) ( In one application I had to go to sheet(1) and etc and getting
the name and seeing if part of it qualified since they change the tab
name EVERY MONTH. If you are going to import the sheet you will either
have to change the tab name to a standard name or change your import
statement for the change in range name.)
2) go to cell A1 and see if it is one of the names you require
a) if it is add 1 to a counter.
3) go to cell B1 and repeat the step 2 process.
4) go through as many columns as you reasonably think they can have.
5) close the spreadsheet
6) At the end, if the counter does not equal the number of required
fields you can then display an error message or form or whatever and
stop the rest of the process.

Danger of above is that two columns could have the same name and access
will not like it So you may want to set flags and change your logic
accordingly. However that then qualifies it for the "Inverse logic
rule" 90% of the code is for the 1% of the occurances.

In general there will probably be a problem with the import into an
existing table if they have added or changed any of the other field
names. So you will probably want to delete the import table and import
to a fresh table. (Then you may have a problem if the process is
aborted since it will not find the import table to delete the next time
through)

This is the voice of sad experience talking about the two possibilities
indicated.

Hope this gave you some ideas.

Ron

 

[John Nurick]

I'd do something like this to open an empty recordset whose Fields get
their names from the column headings:

Dim S As String
Dim R As DAO.Recordset
Dim F As DAO.Field
Dim FileName As String
Dim SheetName As String

...
S = "SELECT * FROM [Excel 8.0;HDR=Yes;Database=" _
& FileName & "].[" & SheetName & "] WHERE False;"

Set R = CurrentDB.OpenRecordset(S, dbopensnapshot)
For Each F in R.Fields
'do stuff with F.Name
...
Next
R.Close

 

[Guest]

Hi John,

I tried out your code, but I found that I needed to append a $ sign onto the
end of the worksheet name, as in:

& FileName & "].[" & SheetName & "$] WHERE False;"

in order to prevent Run-time error '3011':
"The Microsoft Jet database engine could not find the object
'WorksheetName'. Make sure the object exists and that you spell its name and
the path name correctly"

where 'WorksheetName' is the name of the worksheet that I specified.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I'd do something like this to open an empty recordset whose Fields get
their names from the column headings:

Dim S As String
Dim R As DAO.Recordset
Dim F As DAO.Field
Dim FileName As String
Dim SheetName As String

...
S = "SELECT * FROM [Excel 8.0;HDR=Yes;Database=" _
& FileName & "].[" & SheetName & "] WHERE False;"

Set R = CurrentDB.OpenRecordset(S, dbopensnapshot)
For Each F in R.Fields
'do stuff with F.Name
...
Next
R.Close

Is there anyway to programatically confirm that specific field names
exist in the first row of a given Excel spreadsheet from code within
Access? I have code set up to import data from our spreadsheets from
HQ into our Access database for further use, using the first row as
column headings to determine the destination field in the Access table.
Since it's possible (and probable) that HQ could change the format of
the spreadsheets without my knowledge, I'd like to be able to confirm
that the needed fields are present (and named properly) before import.
I'm not very familiar with examining Excel data from within Access.
Any suggestions on how to do that?

Thanks!

 

[google]

"Hope this is usefull."

I don't know how much of that I could have manufactured on my own, but
if I could, it would have taken quite a while.

Thank you very much!

 

[google]

Ooops, I replied to quickly:

"If you do it incorrectly, Access will get really stupid and
create additional instances of Excel, so when you are done, the will be
Excel
Processes running you are not aware of and will cause a hang up if you
try to
open an excel file."

It seems I'm having this problem. The code seems to work correctly
within Access (it identifies fields that don't exist in my table), but
it does seem to leave an excel process running, which prevents me from
opening Excel. How do I close the processes that it opens? I used
your code verbatim, with the following exceptions:

I added the following declarations:
Dim XLApp As Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet
Dim blnExcelWasNotRunning As Boolean
Dim dbs As Database
Dim tdf As TableDef

I added the references to my table:
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("TraneProjSummaryTemp")

I changed:
varGetFileName to a reference to a text box on my form that has the
spreadsheet file path/name.
"Actuals_res_export" with "Sheet1" (they are always single sheet
spreadsheets)

I removed:
Me.txtStatus = "Opening Spreadsheet"
On Error GoTo LoadAdjustedActuals_Err

I think everything else is the same (and as luck would have it, my
spreadsheet had exactly 30 fields). But after it runs, there is still
an excel.exe instance left running, which does cause problems opening a
spreadsheet later.
I also have one other problem... there is a field that it thinks isn't
there that really is. I expect it's because of the fields name... the
field has a single quote in the name: "Proj Dtl SUBK PO's".
Unfortunately, I can't change the field name... the spreadsheets are
created on the fly from a data export (from a web based reporting app),
and that is what HQ has named it. Any suggestions on how to make it
deal with the apostophe?

Thanks!

 

[John Nurick]

Thanks, Tom. I can never remember whether it's sheet names or range
names that need $ appended.

Hi John,

I tried out your code, but I found that I needed to append a $ sign onto the
end of the worksheet name, as in:

& FileName & "].[" & SheetName & "$] WHERE False;"

in order to prevent Run-time error '3011':
"The Microsoft Jet database engine could not find the object
'WorksheetName'. Make sure the object exists and that you spell its name and
the path name correctly"

where 'WorksheetName' is the name of the worksheet that I specified.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I'd do something like this to open an empty recordset whose Fields get
their names from the column headings:

Dim S As String
Dim R As DAO.Recordset
Dim F As DAO.Field
Dim FileName As String
Dim SheetName As String

...
S = "SELECT * FROM [Excel 8.0;HDR=Yes;Database=" _
& FileName & "].[" & SheetName & "] WHERE False;"

Set R = CurrentDB.OpenRecordset(S, dbopensnapshot)
For Each F in R.Fields
'do stuff with F.Name
...
Next
R.Close

Is there anyway to programatically confirm that specific field names
exist in the first row of a given Excel spreadsheet from code within
Access? I have code set up to import data from our spreadsheets from
HQ into our Access database for further use, using the first row as
column headings to determine the destination field in the Access table.
Since it's possible (and probable) that HQ could change the format of
the spreadsheets without my knowledge, I'd like to be able to confirm
that the needed fields are present (and named properly) before import.
I'm not very familiar with examining Excel data from within Access.
Any suggestions on how to do that?

Thanks!

 

[google]

"there is a field that it thinks isn't
there that really is. I expect it's because of the fields name... the
field has a single quote in the name"

As it turns out, it WASN'T the quote that was causing the problem.
What I didn't notice was that the spreadsheet that we get, for that one
field, has a space at the end of the field name (which Access trimmed
when I originally created the table structure by importing the
spreadsheet). I've fixed that problem with:

If fld.Name = Trim(XLSheet.Cells(1, lngFldCtr).Value) Then

So the only remaining problem I have is the fact that Access leaves an
Excel process running once the routine is done, borking Excel
afterwords. If I can just figure out how to close that, I'll be set!

 

[google]

Just wanted to follow up and say that I finally figured out I just
needed to add in the appropriate commands to close the workbook, quite
excel (if it wasn't open before), and if it was open, turn all the
screen updating, etc., back on. I've even made it a function that I
can re-use, which returns a list of missing field names.

So I'm in good shape now.. Thanks so much for the help!