Rename Tables

[Rukgo]

Hi, so I am currently importing a whole folder from my desktop into Access using a macro then run a query on them. Though I want to be able to put any file into this folder and edit the name afterward.

For example there is a table that I upload a weekly document along the lines of HOG-(MMDDYYYY) and I want to try and change it to HOG as I import it in Access.

Here is the current code I am using

 Const msoFileDialogFolderPicker As Long = 4
 Dim objfiledialog As Object
 Dim otable As DAO.TableDef
 Dim strPathFile As String, strFile As String, strpath As String
 Dim strTable As String
 Dim blnHasFieldNames As Boolean

 ' accept excel first line as headers for tables
 blnHasFieldNames = True
 
 'select folder and set path
 Set objfiledialog = Application.FileDialog(msoFileDialogFolderPicker)

With objfiledialog
    .AllowMultiSelect = False
    If .Show Then
     strpath = .SelectedItems(1) & Chr(92)
     End If
End With

 strFile = Dir(strpath & "*.xls")
 'import all files within selected folder
 Do While Len(strFile) > 0
    strPathFile = strpath & strFile
    strTable = Left(strFile, Len(strFile) - 5)
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        strTable, strPathFile, blnHasFieldNames
    strFile = Dir()
 Loop

 

[Micron]

This isn't making sense to me.

Hi, so I am currently importing a whole folder from my desktop into Access using a macro then run a query on them. Though I want to be able to put any file into this folder and edit the name afterward. For example there is a table that I upload a weekly document along the lines of HOG-(MMDDYYYY) and I want to try and change it to HOG as I import it in Access.

Obviously you can put any file into a folder and rename it any time you want.
"there is a table that I upload a weekly document" is confusing as well. Your code limits your choices to xls files, thus precluding any chance of gathering 'documents', I would think.

Does this code return and alter the file name as you wish? I wonder because I don't see how this
Left(strFile, Len(strFile) - 5) turns this
HOG-(MMDDYYYY) into this
HOG, especially since I don't see where the file extension is anymore.
Are there really brackets around the date? If so, that's awful IMO. If all you want is HOG (which by the way, will over write your existing table each time), why not just Left(strFile,3)?