you can do one of two things to solve this extra column issue.
1. In your original excel file remove all rows of 'blanks' In other words, if you have column A, C and E with data, and columns B and D are empty by design, delete columns B and D, if you have no empty columns and the only empty spaces you have are at the END of your rows, highlight all the rows after the last piece of your data and delete those columns not clearing the cells (though that would probably work), delete the columns.
2. Just keep adding FX columns until you don't get the error anymore.
with the transferspreadsheet command you can define a range either by sheet name or sheet name and specific cells
http://msdn.microsoft.com/en-us/libr...ice.15%29.aspx
Added the Range A1L1000 - Still got the F1 error
1. Did it - Still got F1 error
2. Did it - this is what I got
Super weird
do you have a blank row in the first row of your excel spreadsheet
if you do, get rid of it. then try again.
check your excel file, the whole way through to see if you have extra columns somewhere in the data because if that's what your import looks like it screams that there are extra columns you are not aware of in your excel file, what I would suggest is finding a relatively rare value in your F columns (f1 - Fx) and performing a search on the excel file to see if you get an unexpected column.
I'm sure there's nothing there. I already deleted all the rows and columns below my 2 rows of data and after my 12 columns.check your excel file, the whole way through to see if you have extra columns somewhere in the data because if that's what your import looks like it screams that there are extra columns you are not aware of in your excel file, what I would suggest is finding a relatively rare value in your F columns (f1 - Fx) and performing a search on the excel file to see if you get an unexpected column.
Is there some kind of code where I could write
static F1 delete or something like that?
Meaning incremental Fvalue, delete it? or would that be a work around that wouldn't be a good idea?
the problem is there is no F value on your excel spreadsheet, it's in one of your columns Access just assigns an F value to a column that has no first row information. What you need to do is find which columns are in your excel file screwing up your information, or alternately after your import is complete append any values in your F columns to the 'correct' columns, then delete the F columns from your table, but if this is an ongoing process and not a one time deal that is a very heavy handed approach, it would be better to find out what the original problem is and plan accordingly.
Very true.
I mean now my Range is even limited to A:L so Access isn't even allowed to import any other columns.
So I'm not sure why it wants to duplicate the same table next to the original and down 2 rows.
Here's my code again.
Excel TableCode:Sub Import()Dim strTable As String Dim strFilePath As String With Application.FileDialog(msoFileDialogFilePicker) .AllowMultiSelect = False .Title = "Locate a Excel file to Import" .ButtonName = "Import" .Filters.Clear .Filters.Add "Excel", "*.xlsx" .InitialFileName = "C:\Test" .InitialView = msoFileDialogViewThumbnail If .Show = 0 Then 'There is a problem Exit Sub End If 'Save the first file selected strFilePath = Trim(.SelectedItems(1)) End With strTable = "tbl1_PartPricingDataAggregation" DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, strTable, strFilePath, False, "A1:L1000" End Sub
Excel Sheet
Access in Design view (goes to F12)
What Access is doing
The only thing I can think of is that you are using Reserved Words in your Import Process. The reserved words are the field names within your table and the Header names in your Excel spreadsheet, eg Date, Currency, and probably Source and Range too.
But then wouldn't it just duplicate those columns?
Not sure what the question is. My comment is regarding reserved words. Do not use them in Access. They will cause fascinating results.
lol fascinating results, ok, I'll try to modify them
what do you think of this link?
http://computerfixerpeople.com/runti...391-access.php
Have you tried to create a sample file by hand then import it and see what happens? I would wager it imports correctly.
Is the original file a text file? a .csv file or is it an actual .xls/xlsx file?
It makes no sense to me that your file is 1 record long but it is essentially creating 2 records for that one record. The only thing I can think of is that there may be a end of line indicator on text/csv file that is throwing things out of whack, for instance a double line feed instead of a carriage return/line feed or some other artifact of the exported data.
can you post your sample SOURCE file to this board.
That's a good question. The code is specific to xlsx and even the Filepicker should filter to only that type. However, something is amiss. Perhaps to expand on making a new file, you could make a new CSV file and try importing that.
AS for the link in the previous post, do not know why you would need to edit your registry or even consider it.
Have you tried to create a sample file by hand then import it and see what happens? I would wager it imports correctly.
Is the original file a text file? a .csv file or is it an actual .xls/xlsx file?
It makes no sense to me that your file is 1 record long but it is essentially creating 2 records for that one record. The only thing I can think of is that there may be a end of line indicator on text/csv file that is throwing things out of whack, for instance a double line feed instead of a carriage return/line feed or some other artifact of the exported data.
can you post your sample SOURCE file to this board.
Sorry it's been a while. Thanks so much for your help.
My code is for a .xlsx file, it won't work if I choose another file type.
I've tried importing my template .xlsx spreadsheet 2 times now into Access. Both have been met with the same F1, F2, etc error
Here is my code:
Code:Option Compare Database Sub Import() Dim strTable As String Dim strFilePath As String With Application.FileDialog(msoFileDialogFilePicker) .AllowMultiSelect = False .Title = "Locate a Excel file to Import" .ButtonName = "Import" .Filters.Clear .Filters.Add "Excel", "*.xlsx" .InitialFileName = "C:\Test" .InitialView = msoFileDialogViewThumbnail If .Show = 0 Then 'There is a problem Exit Sub End If 'Save the first file selected strFilePath = Trim(.SelectedItems(1)) End With strTable = "tbl1_PartPricingDataAggregation" DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, strTable, strFilePath, False, "A1:L1000" End Sub