Importing from XL with invalid field names

[liddlem]

Hi there
I have a vendor that emails me the results of blood tests on a daily basis.
I wish to import the data into Access, but the field names are not valid MS Access names.
The field names contain reserved names, leading/training spaces, letters and characters etc.

Strangely enough, I am able to link the SSheet as a table, but none of my update and/or import queries work because of the field name issue.

I have tried looking at
A. Import the data (I get errors)
B. Link table and run an update query
C. TransferSpreadSheet method
...but have not had any success with any of these methods.

Any suggestions are welcomed.
Successfull offer will be lauded with praise andf admiration!

 

[hgus393]

There are several ways to import exceldata ignoring the field names. I use a method defining excel as an object and then tell it to import to a table which already exits.

My way is perhaps not the best, but it works for me. Check code below:

Sub ImportBlood()
Dim sPath As String, sFile As String, 
Dim MyXl As Object
 
 
 
 
sPath = "C:\temp" ' or whatever directory you have
 
If Dir(sPath) = "" Then
MsgBox ("Catalog missing!")
Exit Sub
End If
 
 
sFile = Dir$(sPath & "my filename".xlsx") ' your filename goes here
Set MyXl = CreateObject("Excel.Application")
 
If sFile = "" Then
MsgBox ("Filname wrong or missing!")
Exit Sub
End If
 
 
Do While sFile <> ""
 
GetFile sPath, sFile, MyXl
sFile = Dir
Loop
 
Set MyXl = Nothing
 
End Sub
 
Sub GetFile(sPath As String, sFile As String, MyXl As Object)
Dim rs As Recordset
Dim i As Double
Dim rowVector As Variant
Dim sSheet As String
 
Set MyXl = GetObject(sPath & sFile)
 
sSheet = "The sheet name" ' plug in the sheet name
 
Set rs = CurrentDb.OpenRecordset("Your table name") ' table name in access
i = 2 ' Defines that you want to start importing from row 2 in excel
rowVector = MyXl.Worksheets(sSheet).Range("A" & i & ":N" & i).Value ' depending on layout you might need to change the A and the N (ie column's where A is the starting point and N the endpoint)
 
 
Do While rowVector(1, 1) <> ""
rs.AddNew
rs![Field 1] = rowVector(1, 1)
rs![Field 2] = rowVector(1, 2)
rs![Field 3] = rowVector(1, 3)
rs![Field 4] = rowVector(1, 4)
rs![Field 5] = rowVector(1, 5)
rs![Field 6] = rowVector(1, 6)
rs![Field 7] = rowVector(1, 7)
rs![Field 8] = rowVector(1, 8)
rs![Field 9] = rowVector(1, 9)
rs![Field 10] = rowVector(1, 10)
rs![Field 11] = rowVector(1, 11)
rs![Field 12] = rowVector(1, 12)
rs![Field 13] = rowVector(1, 13)
rs![Field 14] = rowVector(1, 14)
rs.Update
i = i + 1
rowVector = MyXl.Worksheets(sSheet).Range("A" & i & ":N" & i).Value' depending on layout you might need to change the A and the N (ie column's where A is the starting point and N the endpoint)
Loop
 
MyXl.Close SaveChanges:=False
End Sub

Hi there
I have a vendor that emails me the results of blood tests on a daily basis.
I wish to import the data into Access, but the field names are not valid MS Access names.
The field names contain reserved names, leading/training spaces, letters and characters etc.

Strangely enough, I am able to link the SSheet as a table, but none of my update and/or import queries work because of the field name issue.

I have tried looking at
A. Import the data (I get errors)
B. Link table and run an update query
C. TransferSpreadSheet method
...but have not had any success with any of these methods.

Any suggestions are welcomed.
Successfull offer will be lauded with praise andf admiration!

 

[vbaInet]

See the following link for methods of exporting to Excel:

http://www.btabdevelopment.com/ts/default.aspx?PageId=10

 

[liddlem]

Hi HGUS393
Thank you for your quick response. I only managed to try using the code earlier today.
Initially, I had a problem when the code got to the second record, but after a restart (of the PC) - it seems like its been resolved.

Thanks a ton. Once again, I have learned a very valuable "trick"
Much appreaciated.