DoCmd.TransferSpreadsheet no longer trims trailing spaces from field names after conversion to Office 365

[megan5marie]

Hello and thank you in advance for any help you can give me.

I was recently converted to Office 365, and as a result, DoCmd.TransferSpreadsheet no longer trims trailing spaces from field names.

I have a database that imports a single file from a client. (Please see form code below.) The filenames are never the same, so I don't believe linking instead of importing is an option.

There is one field name in the client files that sometimes has a trailing space and sometimes doesn't. (Asking the client to keep this consistent is not an option.) Until conversion to Office 365, this was not an issue; the trailing space were trimmed automatically upon import. Is there any way to have trailing spaces in field names trimmed upon DoCmd.TransferSpreadsheet with Office 365?

Thank you.

Private Sub btn_ImportDataFile_Click()

    Dim Response As Integer
    Dim fd As FileDialog
    Dim fileName As String
    
    Response = MsgBox("This will import a data file into the system.  Do you wish to proceed?", vbYesNo, "Import Data File")
        
    If Response = vbYes Then
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        fd.AllowMultiSelect = False
        fd.Filters.Clear
        fd.Filters.Add "Client Data File", "*.xlsx"
        fd.Filters.Add "Client Data File", "*.xls"
        If fd.Show = True Then
            If fd.SelectedItems(1) <> vbNullString Then
                fileName = fd.SelectedItems(1)
            End If
        End If
        
        If fileName <> vbNullString Then
            DoCmd.SetWarnings (WarningsOff)
            DoCmd.TransferSpreadsheet transferType:=acImport, TableName:="DataFile", fileName:=fileName, hasfieldnames:=True


            Response = MsgBox("Data has been imported.", vbOKOnly, "Import Data File")
        End If
    End If       

End Sub

 

[megan5marie]

I could not find any way to trim the trailing spaces upon import, but I worked around the issue by adding this code before the DoCmd.TransferSpreadsheet line. (The field name in question is in cell Z1 of the source file.)

Dim xlapp As Object
        Dim xlBook As Object
        Dim xlSheet As Object
        Dim ExcelPath As String
    
        ExcelPath = fileName
        Set xlapp = CreateObject("Excel.Application")
        Set xlBook = xlapp.Workbooks.Open(fileName)
        Set xlSheet = xlBook.ActiveSheet
    
        xlapp.Visible = False
        
        xlSheet.Range("Z1").Value = "[field name without trailing space]"
        
        xlBook.Save
        xlBook.Close
        
        Set xlSheet = Nothing
        Set xlBook = Nothing
        Set xlapp = Nothing

 

[Gasman]

Could always delete old file and rename current file to the linked filename?

 

[CJ_London]

other way is to specify the range without headers. So if headers are on row 1 and there are 4 columns A, specify the range as A2

This will give default headers of F1, F2, F3 and F4

 

[megan5marie]

Could always delete old file and rename current file to the linked filename?

Thank you but that wouldn't work because the field name in question could still have the leading zero. (Edit: I mean trailing space, not leading zero. Scatterbrained!)

 

[megan5marie]

other way is to specify the range without headers. So if headers are on row 1 and there are 4 columns A, specify the range as A2

This will give default headers of F1, F2, F3 and F4

Thank you CJ! That sounds more elegant than my workaround. Maybe a dumb question but what if I don't know how many rows of data there are to import? It varies. Can I still specify the range?

 

[Gasman]

Leading zero?
I thought we were talking about trailing spaces?
Anyway I was just referring to your comment

The filenames are never the same, so I don't believe linking instead of importing is an option.

 

[megan5marie]

Leading zero?
I thought we were talking about trailing spaces?
Anyway I was just referring to your comment

Sorry, working on too many things at once! Yes we are dealing with a trailing space. It would still be there if I saved over the linked file.

 

[Gasman]

But then you could use Trim as you brought it in?

Annoying I know, but I am just thinking of workarounds for something that no longer works in 365.

 

[megan5marie]

But then you could use Trim as you brought it in?

Annoying I know, but I am just thinking of workarounds for something that no longer works in 365.

What does "use Trim as you brought it in" mean? The field name would still vary from one linked file to another (according to the file saved over it).

Edit: I don't think you can use an expression on an unknown field name, if that's what you're saying.

 

[Gasman]

What does "use Trim as you brought it in" mean? The field name would still vary from one linked file to another (according to the file saved over it).

Edit: I don't think you can use an expression on an unknown field name, if that's what you're saying.

No, I was thinking along CJ_London's lines with just F1 to F whatever, or just refer to the field positions?

Now, knowing your client, they will probably change the order.

 

[megan5marie]

No, I was thinking along CJ_London's lines with just F1 to F whatever, or just refer to the field positions?

Now, knowing your client, they will probably change the order.

How would I use F1, F2, etc. if I'm saving over the linked file? Unless you mean exactly what CJ suggested?

Not sure what "just refer to the field positions" means either. But I appreciate all the replies and your time. Thank you!

 

[Gasman]

How would I use F1, F2, etc. if I'm saving over the linked file? Unless you mean exactly what CJ suggested?

Not sure what "just refer to the field positions" means either. But I appreciate all the replies and your time. Thank you!

Yes forget the field positions, I was thinking of recordsets. Sorry.