Solved TransferSpreadsheet Issue - Periods In Field Names

[EzGoingKev]

Good morning.

I receive an Excel file from another department in my company. I cannot have the field names changed - it is what it is.

The Excel file has data from A to G.

Row 1 has the effective date so it will have "Effective July 1st 2024" in Column G.

Row 2 has the field names. Two of the fields have periods at the end of them:
- Customer Part No.
- MPA Part No.

My table does not have the periods in them as you cannot use the periods in field names in Access.

If I manually go in and remove the periods from the two field names this works 100%:

DoCmd.TransferSpreadsheet acImport, 10, "table_name", strPath, True, Range:="excel_sheet_name!A2:G5000"

Is there something I can do so I do not have to go into the file manually and remove the periods?

 

[theDBguy]

Well, if the periods must be removed and you don't want to do it manually, then one approach is to do it using Excel Automation.

 

[gemma-the-husky]

Good morning.

I receive an Excel file from another department in my company. I cannot have the field names changed - it is what it is.

The Excel file has data from A to G.

Row 1 has the effective date so it will have "Effective July 1st 2024" in Column G.

Row 2 has the field names. Two of the fields have periods at the end of them:
- Customer Part No.
- MPA Part No.

My table does not have the periods in them as you cannot use the periods in field names in Access.

If I manually go in and remove the periods from the two field names this works 100%:

DoCmd.TransferSpreadsheet acImport, 10, "table_name", strPath, True, Range:="excel_sheet_name!A2:G5000"

Is there something I can do so I do not have to go into the file manually and remove the periods?

How do you process it at the moment?
Can you not do something similar but massage the bits that contain the dates?

 

[Gasman]

I cannot have the field names changed - it is what it is

Surely it is yours to deal with when you are sent what I am assuming is a copy or some export?

 

[EzGoingKev]

Well, if the periods must be removed and you don't want to do it manually, then one approach is to do it using Excel Automation.

The Excel files are supposed to come every month but they only come when there is a change. Is Excel Automation something I can add to the VBA in my Access database so it will work on the new sheets as they come?

 

[EzGoingKev]

How do you process it at the moment?
Can you not do something similar but massage the bits that contain the dates?

I am fairly new at this company and this is the second one I have received.

Initially I deleted row 1 and imported it manually to get the table created. Access dropped the periods in the field names during the import. I did not notice there were periods in the name until I tried to use the VBA to import it into the table.

I want to run the code and have Access do all the work for me.

 

[EzGoingKev]

Surely it is yours to deal with when you are sent what I am assuming is a copy or some export?

It is mine to deal with so that is what brought me here.

Sometimes when questions like this are asked the replies become more about the way it should be instead of the way it is. I am stuck with the file they send me. They are not going to change it just for me.

 

[jdraw]

Kev,
How did they deal with this before you joined the company? Or is this some new process since your arrival?

 

[theDBguy]

The Excel files are supposed to come every month but they only come when there is a change. Is Excel Automation something I can add to the VBA in my Access database so it will work on the new sheets as they come?

Yes, Excel Automation is done through VBA.

 

[Gasman]

It is mine to deal with so that is what brought me here.

Sometimes when questions like this are asked the replies become more about the way it should be instead of the way it is. I am stuck with the file they send me. They are not going to change it just for me.

So open the file, use Replace() to remove the full stops. Save the file and process as normal.
All can be done with Excel automation.

excel automation from access - Google Search

 

[EzGoingKev]

Kev,
How did they deal with this before you joined the company? Or is this some new process since your arrival?

IDK. The people I do see stuff use VLOOKUP for everything.

I am not concerned about how they used to it, I am focused on how I want to do it.

 

[jdraw]

Kev,

That wasn't my intent. If they use VLookup everywhere, then maybe they were limited to Excel. Perhaps you are introducing the use of Access --your method to do it -and that was never done before. So from a context perspective --this could be a new process, and you are discovering some "incompatibilities".

 

[gemma-the-husky]

One alternative might be to import the spreadsheet without the header rows. If you do that you rely on the order of the columns being correct.

Another approach is to change the excel sheet into a CSV. CSVs are better in many ways. You get far more control with a CSV. Could they send you a CSV rather than an actual xls file.

 

[EzGoingKev]

One alternative might be to import the spreadsheet without the header rows. If you do that you rely on the order of the columns being correct.

That did not work.

 

[The_Doc_Man]

If you don't want to have to remove the periods manually each time, but Access is GOING to remove the periods anyway, can you clarify the end goal in some detail? Are you allowed to import to Access in a way that you drop the periods? Or is there a later reconciliation for which you need the periods to still be there? We are trying to evaluate our concern about what does and doesn't need to be preserved.

If you want something more than this, don't hesitate to ask, but I'm going by this part of your request:

Is there something I can do so I do not have to go into the file manually and remove the periods?

Here is the simplest approach if you don't need to preserve the periods. Read this link to the VBA FileCopy command.

FileCopy statement (VBA)

Office VBA reference topic

learn.microsoft.com

Make a copy of the original file. Using an Excel Application Object, step into the file and scan your first row to find the fields that contain the periods. Since you have some predictability regarding which columns to examine AND the expected position of the periods, this shouldn't take too long since you only have at most 7 cells to search (A1-G1, based on your original question and description.) Then do a close/save on the Excel App object. You can also do an Application.Quit on the Excel object now, because after that, you should be able to do the direct import. You can also delete the copy that you made once the import of the copy is complete.

NOTE: if you don't need to preserve the file you get because it IS a copy already, then skip the FileCopy operation and just open the file you got. The rest of my discussion applies either to the copy or the original version.

Here is a link showing an example of opening an Excel file in VBA from Access.

Redirecting

Here is a link showing an example of saving and closing an Excel file in VBA from Access.

Close Excel file from Access vba

I have created an excel xls file from Access vba. Private Sub ModifyXl() Dim XLapp As Excel.Application Dim xlWB As Excel.Workbook Set XLapp = New Excel.Application Dim xlSh As Excel.Worksheet ...

stackoverflow.com

Here is a link showing an example of editing an Excel spreadsheet from Access VBA.

Using Access VBA to edit and format excel spreadsheet - Microsoft Q&A

Hi. Currently I have an access database outputting to excel and it works. However, the spreadsheet is rather plain and the columns aren't the right width, the fields that have a date\time only show date etc. I want to edit the spreadsheet after I…

learn.microsoft.com

Time-wise, this might take as much as two or three seconds if the network gets involved - but probably if all files are local, then it should be even faster because you won't be scrolling through the file that much. One row to modify out of a few thousand is, essentially, chump change for modern processors.

 

[CJ_London]

You can use sql instead of transferspreadsheet

example xlsx file

using this sql

SELECT *
FROM [sheet1$] AS xlData IN 'D:\Dev\1_temp\badheaders.xlsx'[Excel 8.0;HDR=yes;IMEX=1;ACCDB=Yes];

you get

spaces not affected, but the . in the second column is replaced with a # and the linefeed with a _

Alternatively use this sql to get a better result to remove or replace other characters

SELECT XL.[one bad header] AS onebadheader, XL.[and another#] AS andAnother, XL.[Y/N] AS YN, XL.[and one with a_linefeed] AS withLinefeed
FROM (SELECT * FROM [sheet1$] AS xlData IN 'D:\Dev\1_temp\badheaders.xlsx'[Excel 8.0;HDR=yes;IMEX=1;ACCDB=Yes])  AS XL;

This is the equivalent of using transferspreadsheet to link to a file - you can then use it to import, join to other tables etc.

 

[EzGoingKev]

I figured it out.

Two of the fields have periods at the end of them:
- Customer Part No.
- MPA Part No.

When I ran my code I would get an error that it could not find the field "Customer Part No#". It changed the period to the number sign.

I went into the table and added the number sign to the end of the table names. It ran with no issues.

 

[Gasman]

It is not recommended to have fieldnames with such characters?
Could you not just link to the file and use a query to pull the data in?