change cell values of Excel import file 2

[robertsquestion]

Hi,

I'm using MS Office 2003 and have got a question about importing an Excel file with VBA into an MS Access table.

The Excel file needs to be imported once a week. It's exported from another system to a default folder and the Excel file always gets the same name (let 's say 'data.xls'). Row 1 contains the following values (fieldnames):
A1 = customernr.
B1 = materialnr
C1 = sales
D1 = districtnr.
The rows starting from row 2 contain the actual data.

Now my Access table ('T_data') contains the following fields:
customernr
materialnr
sales
districtnr
So that's exactly the same as row 1 of the excel file, except for A1 and D1 because this fieldname contains a dot in the excel file.

Now I would like to import the excel file using 'TransferSpreadsheet'. Is there a way to adjust the excel cells A1 and D1 with (MS Access)VBA before importing the excel file? So the preferred steps would be:
1. check if cell value A1 = customernr. => if so, replace cell value A1 = customernr
2. check if cell value D1 = districtnr. => if so, replace cell value D1 = districtnr
3. import the adjusted file in table 'T_data'

I know that if you manually import the file ('Get external data') Access automatically removes the dots. But the goal is to import the file with VBA.

I hope someone out there can help me out!

Thanks,
Robert
The Netherlands

 

[PHV]

Access automatically removes the dots
So, have you tried the DoCmd.TransferSpreadsheet method ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robertsquestion]

Hi PHV,

Yes, I've tried 'TransferSpreadsheet' but that doesn't work. I forgot to mention that MS Access automatically removes the dots when you import the Excel file manually (via 'Get external data') into a new(!) table. So for the first file that you import. But if you try to import a second file in the existing table that was created with step 1, I get an error.
The error that I get if I use 'Get external data' is:
'An error occurred trying to import file 'C:\Data.xls', the file was not imported'
The error that I get if I use 'TransferSpreadsheet' is:
'Field 'customernr#' doesn't exist in table 'T_data'

I hope my explanation is detailed enough. Do you have any idea how to solve this?

Thanks,
Robert

 

[PHV]

You may try to use a linked table and an append query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Why not change the Field Names in Access to include the DOT?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robertsquestion]

Hi PHV,

Very creative, using linked tables and an append query works!
So it's not possible to adjust the first Excel-record with VBA ? The Excel file should get the same name ('Data.xls') every week, but it's a manual thing (the users have to enter the filename when they export it from the other system). I would like to make the solution as 'dummy-proof' as possible. If I use linked tables, I will get in trouble if the user enters a wrong filename for the excel file.

Do you have another suggestion for this issue?

Thanks,
Robert

 

[robertsquestion]

Hi SkipVought,

Changing the Field Names in Access (2003) to include the DOT would indeed be the best solution. But if I try that, I get the message:
'The field name is not valid. Make sure that the name doesn't contain a period (.), exclamation point(!), etc. etc.'

Or is there a way around this error message?

Thanks,
Robert

 

[boblarson]

Putting a dot (or other special characters) in the table name is one of the WORST things you can do (shame on you Skip for even suggesting that).

If linking hadn't worked for you, you could use the Excel common object model to do what you needed. It is actually very simple code:

Dim objXL As Object

Set objXL = CreateObject("Excel.Application")

With objXL
.Workbooks.Open("FilePathAndNameHere")
.Sheets(1).Range("A1").Value = Replace(.Sheets(1).Range("A1").Value, ".", "")
.Sheets(1).Range("D1").Value = Replace(.Sheets(1).Range("D1").Value, ".", "")
.ActiveWorkbook.Save
.ActiveWorkbook.Close
.Application.Quit
End With

And then use your code.


Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[SkipVought]

I agree!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robertsquestion]

Hi Bob,

That's what I was looking for. I've tested it and it works great, thanks a lot!

Robert