Saved Imoorts problem with file name

I tried different ways of renaming the xlsx file and none worked. One thing that stands out to me is that Acceess defaults to the original file name when creating the table during the import. I am not at all sure where access is getting that information as the table is already renamed before I try to import it. Maybe this is a known problem?

The import specs are saved in two system tables that you could review. I believe the table names begin with msysIMEX….

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

In this particular case and instance, what I would do:
[ul]
[li]Open your Excel file with your data that causes the issue[/li]
[li]Open another fresh, empty Excel file[/li]
[li]Copy data from your original Excel file[/li]
[li]Paste the data into your ‘fresh’ empty Excel file[/li]
[li]Close your original Excel file and Delete it in Windows Explorer[/li]
[li]Save your remaining Excel file with the name you want[/li]
[li]Do your Access Import again[/li]
[/ul]

This way the new Excel file should have no association with any previous, old, problematic names.

Just a suggestion...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

Andy, Great suggestion from a troubleshooting standpoint. Abandoning the saved import and doing a new step by step import would be faster though.
dhookum, I will see if I can find out how to access system tables.

I just setup a VBA import procedure that works great, except I get errors because of the one field that defaults to "double" instead of "short text". the values are mostly digits as they are account numbers, but they create havoc when importing a table.

So the VBA procedure fixes the filename problem, but I'm back to a datatype problem.

I found a great video that offered the following code for doing an import. I wonder if there is anywhere I can use VBA to handle the data type.

Option Compare Database
Option Explicit

Public Sub ImportExcelSpreadsheet(filename As String, tableName As String)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tableName, filename, True
End Sub



Private Sub btnBrowse_Click()
Dim diag As Office.FileDialog
Dim item As Variant

Set diag = Application.FileDialog(msoFileDialogFilePicker)
diag.AllowMultiSelect = False
diag.Title = "Please select an Excel Spreadsheet"
diag.Filters.Clear
diag.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx"

If diag.Show Then
For Each item In diag.SelectedItems
Me.txtFileName = item
Next
End If
End Sub

Private Sub btnImportSpreadsheet_Click()
Dim FSO As New FileSystemObject

If Nz(Me.txtFileName, "") = "" Then
MsgBox "Please select a file!"
Exit Sub
End If

If FSO.FileExists(Nz(Me.txtFileName)) Then
ExcelImport.ImportExcelSpreadsheet Me.txtFileName, "tblImportTemp"
Else
MsgBox "File not found"
End If

End Sub

Ben Clothier has an article that might help at
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

I will check it out... Thank you!

dhookum, you definitely pointed me in the right direction. In fact, I went back to the first in the series of articles and I am very intrigued after reading a couple of paragraphs...
The first article is found at and it really got my attention. Thanks!

This is just my personal opinion, but...
"download a table as a csv from a third party and then save it as an xlsx file"
Depending how you do it, you may run into an issue of Excel 'interpreting' some data NOT the way you would expect or want.
CSV is just a Comma Separated Value text file that can be easily read by VBA and all elements could be easily inserted into your [tt]tblImportTemp[/tt] table, no need for Access' build-in [tt]DoCmd.TransferSpreadsheet[/tt] functionality.
Yes, I know - it is nice to have it ready to use so you don't have to re-invent the wheel, and if it works the way you want - great. But, if it does not, like in your situation, then your own code of transferring the data straight from CSV file into a table, with your full control, sounds like a better solution.

Just my opinion...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

Andy, I have run into a wall and will definitely take your advice. If I can bring the csv into an access table using vba, it would solve a lot of problems. My only concern is that tblImportTemp is the source of an append query. It should not make any difference though. I will report back how I make out. I was able to open the excel table using the article that Dhookum provided to me, but I was only able to get the records to "print" in the immediate window. I was not exactly sure how to pass that along to a table object. The csv import might be easier.

Let's say this is your [tt]C:\TMP\MyCSVFile.csv[/tt] file with 3 fields (no header):

[pre]
123,Bob,987
234,Bill,345
345,Susie,765
456,Andy,463
567,JayRo,646
567,TheEnd,987[/pre]

and you want to insert those records into your Access table named [tt]MyAccessTable[/tt] with 3 fields defined as number, text, and number

So you get:[tt]
Insert Into MyAccessTable Values (123, 'Bob', 987)
Insert Into MyAccessTable Values (234, 'Bill', 345)
Insert Into MyAccessTable Values (345, 'Susie', 765)
Insert Into MyAccessTable Values (456, 'Andy', 463)
Insert Into MyAccessTable Values (567, 'JayRo', 646)
Insert Into MyAccessTable Values (567, 'TheEnd', 987)[/tt]
Instead of [blue]Debug.Print[/blue], just [tt]Execute[/tt] these Insert statements in Access.
No Excel needed and you are in full control.



---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson