VBA Importing long file names

Question

I have a .csv file on my Desktop. The file name is very long and includes special characters such as brackets, e.g.:

[ABCD 012015] ACCT 1117 - Section A10 Grades-20150316_1937-comma_separated.csv

where the first 36 characters of the filename are a constant. The remainder of the file name changes upon each instance of a download. I have tried VBA to import the file into an Access with a DoCmd.TransferText and I get errors. I have discovered that there is a limit to the length of a filename that the DoCmd.TransfertText can handle. So, I need to first rename the file manually, then I can use VBA. Renaming a file is relatively easy with VBA, but I would like to use the info provided in the original filename such as Section A10 (which would make it unique, since there are other Sections labelled A01, A02, etc.) and rename the .csv file as A10.csv, so this means searching for a string AND replacing it. Since the Section can be different, how do I write the code and rename the file then import it with VBA? So, far I have bits and pieces, but cannot put them together:

Name OldPathName As NewPathName

DoCmd.TransferText acImport, "AME_Grades", strTable, strPathFile, blnHasFieldNames

I am using an import specification AME_Grades to make it cleaner in the Access table. Any suggestions? TIA

What have you tried till now? You can use FileCopy command ;) See: msdn.microsoft.com/en-us/library/2s1c774y%28v=vs.90%29.aspx — Maciej Los
I have tried some snipplets of code here and there, but nothing comprehensive. You suggestion helps. Thank you. But, how is it different than the NAME OldPathName As NewPathName? — NGusCar
Also, I think I am more in need of a way (method) to extract the Section number from the file name. This would help in making sure the file is imported as a unique Table in Access with the DoCmd.Transfertext. — NGusCar

Maciej Los Maciej Los · Accepted Answer · 2015-03-17T22:18:55

I'd suggest to use FileCopy function before you start importing data from csv file into MS Access.

Dim OldPathName As String, NewPathName As String
OldPathName = "FullPathToVeryVeryLonLongLongFileName.csv"
NewPathName = "FullPathToShortFileName.csv"
FileCopy OldPathName, NewPathName  
DoCmd.TransferText acImport, "AME_Grades", strTable, NewPathName, blnHasFieldName

VBA Importing long file names

1 Answers