Page 3 of 5 FirstFirst 12345 LastLast
Results 31 to 45 of 66
  1. #31
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88

    Quote Originally Posted by rpeare View Post
    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
    Click image for larger version. 

Name:	Error.JPG 
Views:	16 
Size:	39.6 KB 
ID:	16688

    Super weird

  2. #32
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    do you have a blank row in the first row of your excel spreadsheet
    if you do, get rid of it. then try again.

  3. #33
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Quote Originally Posted by rpeare View Post
    do you have a blank row in the first row of your excel spreadsheet
    if you do, get rid of it. then try again.
    Nope, the first line in Excel consists of my column headers

  4. #34
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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.

  5. #35
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Quote Originally Posted by rpeare View Post
    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.

  6. #36
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    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?

  7. #37
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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.

  8. #38
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    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.

    Code:
    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 Table
    Click image for larger version. 

Name:	Excel.JPG 
Views:	14 
Size:	23.9 KB 
ID:	16691
    Excel Sheet
    Click image for larger version. 

Name:	Excel Sheet.JPG 
Views:	14 
Size:	9.5 KB 
ID:	16692

    Access in Design view (goes to F12)
    Click image for larger version. 

Name:	Access.JPG 
Views:	14 
Size:	38.0 KB 
ID:	16693


    What Access is doing
    Click image for larger version. 

Name:	Error.JPG 
Views:	14 
Size:	39.6 KB 
ID:	16690

  9. #39
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    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.

  10. #40
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    But then wouldn't it just duplicate those columns?

  11. #41
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Not sure what the question is. My comment is regarding reserved words. Do not use them in Access. They will cause fascinating results.

  12. #42
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    lol fascinating results, ok, I'll try to modify them
    what do you think of this link?
    http://computerfixerpeople.com/runti...391-access.php

  13. #43
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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.

  14. #44
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by rpeare View Post
    ...Is the original file a text file? a .csv file or is it an actual .xls/xlsx file?...
    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.

  15. #45
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Quote Originally Posted by rpeare View Post
    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

Page 3 of 5 FirstFirst 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 01-30-2014, 05:26 PM
  2. Replies: 11
    Last Post: 12-11-2013, 10:35 AM
  3. Automate Attendance
    By Palomino33 in forum Access
    Replies: 3
    Last Post: 01-14-2012, 03:57 PM
  4. Automate....
    By kusamharsha in forum Queries
    Replies: 3
    Last Post: 05-14-2009, 03:02 AM
  5. Replies: 1
    Last Post: 09-06-2006, 11:48 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums