0

Long story short, I am dealing with an excel files, which need to be modified a little bit. As the files are coming on weekly basis, I decided to write a simple program via Access, which will help me to make the process fully automatic.

The first step was to upload the excel file into an Access database. I managed to achieve that by creating a custom function and inside to just use the "DoCMD.TransferSpreadsheet acImport" aproach.

The second step was to create two queries and update the table that I just uploaded. That was also pretty straight forward too.

However, the third step is what I am struggling with. Now when the table is updated I wanted to export it back to .xlsx format. However, when I do that no matter if I do it manually via the "External Data" tab or simply use "DoCMD.TransferText acExport" approach I noticed that a few columns that have a space after the end of the string are trimmed automatically. For example, original:"string ", but after exporting it is changed to "string".

I would be really grateful if someone can tell me how to specify to Access that the space after the string is intended and not done by mistake? Preferably with a VBA solution than having to do it manually. Thank you in advance for the help!

PS: I know that .CSV format would be way better, but sadly I need it to be in a XLSX format.

George Smith
  • 415
  • 8
  • 25
  • What specifications do you use with the `docmd.transfertext` command? Also, check this answer: https://stackoverflow.com/a/1851121/1521579 – Ricardo Diaz Jan 05 '21 at 14:15
  • Access does not save trailing spaces in fields by default. So why are you expecting any in exported data? – June7 Jan 05 '21 at 21:34
  • I couldn't duplicate the problem. if in the Excel file you have string (3 spaces after string but no string delimeter) then access strips trailing spaces on import. When you check carefully the spaces are already gone in Access. If in the Excel file you have "string " ( you have marked this is a literal string using " " hence the spaces are important) then the spaces are preserved on both import and export – mazoula Jan 06 '21 at 05:59

0 Answers0