Hidden 'F4' Field

[radon]

I have been trying to add several excel files into one table in access but as the files are being imported access always hangs up on one file. All the excel files are the same version ('07, so is access) and they all have 3 columns. Access hangs up though on this one file telling me that field 'F4' does not exist. I have highlighted all the columns to the right of the first 3 used columns and hit delete but my work still gets derailed with the same file. Anybody have any ideas about why access is seeing this phantom fourth coumn?

 

[boblarson]

Can you post the Excel file?

 

[radon]

I would but apparently I can't upload a .xlsx file, I'll post it in .xls but I don't know if that would change anything.

I also uploaded the next excel file as well because when I upload in access it does import all of the first file, maybe some of that made sense.

 

[radon]

I also wanted to post the code that is associated with my problem. The first set reformats the data into the 3 column format I mentioned earlier and then the second is supposed to import all the reformatted excel files into a table.

Private Sub cmd_process_xls_Click()
On Error GoTo Err_cmd_process_xls_Click

' Switch off all the WARNING msgs.
DoCmd.SetWarnings False

Dim strXlsFileName As String
Dim XlsPath As String
Dim WBname As String
Dim Trimname As String
Dim Firstthree As String
Dim UFirstthree As String
Dim TestBook As Workbook
Dim TestSheet As Worksheet
Dim finalrow As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer

MsgBox "Please verify that all the file names begin with the names of the respective sites."
MsgBox "You are going to process the XLS files. This command will delete the non-data columns and rows and will add Site Names to respective files."
XlsPath = xls_file_path.Value

strXlsFileName = Dir(XlsPath & "*.xlsx")
Do While strXlsFileName <> ""

WBname = strXlsFileName
Trimname = LTrim(WBname)
Firstthree = Left(Trimname, 3)
UFirstthree = UCase(Firstthree)
If (StrComp(UFirstthree, "WHI", vbTextCompare) = 0) Then
UFirstthree = "WHT"
End If
If (StrComp(UFirstthree, "BOW", vbTextCompare) = 0) Then
UFirstthree = "BOW"
End If
If (StrComp(UFirstthree, "CON", vbTextCompare) = 0) Then
UFirstthree = "CON"
End If
If (StrComp(UFirstthree, "HUN", vbTextCompare) = 0) Then
UFirstthree = "HUN"
End If
If (StrComp(UFirstthree, "WOL", vbTextCompare) = 0) Then
UFirstthree = "WOL"
End If
If (StrComp(UFirstthree, "DAV", vbTextCompare) = 0) Then
UFirstthree = "DAV"
End If

Set TestBook = Workbooks.Open(XlsPath & strXlsFileName)
Set TestSheet = TestBook.Worksheets(1)

' Delete First Row in the XLS
Cells(1, 1).EntireRow.Delete

' Last 2 columns in XLS
For k = 1 To 2
Cells(1, 3).EntireColumn.Delete
Next k

finalrow = TestSheet.Range("A65000").End(xlUp).Row

For i = 1 To finalrow
TestSheet.Cells(i, 3) = UFirstthree
Next i
TestBook.Application.DisplayAlerts = False
TestBook.Save
TestBook.Application.Quit
strXlsFileName = Dir()

Loop
MsgBox "Data processing successful."

' Switch on the WARNING msgs.
DoCmd.SetWarnings True
Exit_cmd_process_xls_Click:
Exit Sub
Err_cmd_process_xls_Click:
MsgBox Err.Description
Resume Exit_cmd_process_xls_Click
End Sub

Private Sub Cmd_Upload_Data_Click()
On Error GoTo Err_Cmd_Upload_Data_Click

' Switch off all the WARNING msgs.
DoCmd.SetWarnings True

Dim strFileName As String
Dim cstrPath As String

DoCmd.OpenQuery "Query_del_temp_load_tab"
MsgBox "Temp Table Deleted"
cstrPath = txt_box_file_path.Value
'MsgBox " File Path " & cstrPath
strFileName = Dir(cstrPath & "*.xlsx")
MsgBox "Excel Name" & strFileName
Do While strFileName <> ""
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblTempLoadXls", txt_box_file_path & strFileName
strFileName = Dir()
Loop
MsgBox "Data upload Successful."
' Switch on the WARNING msgs.
DoCmd.SetWarnings True
Exit_Cmd_Upload_Data_Click:
Exit Sub
Err_Cmd_Upload_Data_Click:
MsgBox Err.Description
Resume Exit_Cmd_Upload_Data_Click
End Sub

 

[David Eagar]

Simpler solution might be to highlight all the rows and columns not required in the EXCEL sheet and delete them. Many times Access recognizes cells that used to conatin data but apparently don't anymore.

 

[gemma-the-husky]

I agree with David's posting above

although it LOOKS like the excel files have 3 columns, my bet is at some point one of them has used a fourth column

excel seems to keep a reference to this., and then access loads this 4th (or 5th etc etc) column - Have a look at the imported table to see what actually came across. Try manually importing to a new table, rather than loading to an existing table to see waht columns are there

Either try deleting the columnd D:FZ or whatever in the offending excel sheet, or try exporting to csv's first. Its a bit swkward if someone else is preparing the spreadsheet though, as it means more work for you to keep tidying it.

 

[radon]

I have tried to delete the 4th plus column. The original excel file did have more than the three columns, the code for Private Sub cmd_process_xls_Click() has a section where it is supposed to delete the 4th and 5th coumn. If I cut and pasted the three coumns I want into a new excel file and try to import them should I bypass this issue? Some of your advice is a little over my head, I'm new to Access.

I do have a new question though, when some of the excel files were added to the table only some of the information from the excel file would be imported. For instance only rows 29 through whatever would be added. That has happened on several occations. I would like to know if any of you guys know why that might be.

 

[gemma-the-husky]

Importing Excel can be a bit of a pain - if there is a mixture of text and values, in a column, Access can mistakenly decide for itself based on the higher rows, to use the wrong datatype, and as a result fail to import data - or there might be table validations/keys that prevent some of the rows loading - but there should be an error table created

if you save the excel as a csv, then import the csv, you actually have far more control over the import.

 

[David Eagar]

Try formatting all the excel columns as 'text'. I have found this inavariably overcomes import problems into access

 

[radon]

Does Access see the text files in the same way it sees the excel files. Meaning, from Access' point of view, is there any difference?

 

[gemma-the-husky]

it sees excel according to internal MS rules, which sometimes go wrong, and its hard to force it to do what you want

with a csv you can manually specify which columns you want or dont want, and exactly what format each of those columns should be.

You do this in a specification file, which you can then also use to output data in the same form. You don't have the power of these specification forms with Excel

There are differences between the way each option treats column heads - with a spreadsheet, the column heads mean that the heads HAVE to be there, but the order is not siginificant

With a spec, the order is significant, and the column heads are not needed

-----
for what its worth, i definitely prefer to control a csv file