Page 2 of 5 FirstFirst 12345 LastLast
Results 16 to 30 of 66

Need Code to Automate ImportExportSpreadsheets

  1. #16
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    ok, now it's highlighting the Sub Import() in yellow and DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, "strTable", "strFilePath", TrueSub Import() in gray
    thanks again for helping me


    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 = "Market Data"
        .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", TrueSub Import()
    
    
    End Sub
    End Sub


  2. #17
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    This
    Code:
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, "strTable", "strFilePath", TrueSub Import()
    
    
    End Sub
    End Sub
    needs to be
    Code:
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, "strTable", "strFilePath", True
    
    End Sub

  3. #18
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Click image for larger version. 

Name:	ERROR.JPG 
Views:	22 
Size:	33.7 KB 
ID:	16665
    So it finally ran!!!
    The dialogue box asked me to input the file which was great.
    But then I got this box

  4. #19
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    This:

    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, "strTable", "strFilePath", True

    Needs to be this:

    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, strTable, strFilePath, True

    you don't use quotes if you are inserting a variable

  5. #20
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    alrighty, I got rid of the quotes
    it's still running, but now it's not appending. I got this message
    Click image for larger version. 

Name:	ERROR.JPG 
Views:	21 
Size:	47.6 KB 
ID:	16666

    I don't know if this will help, but here is a picture of my excel columns and access columns
    Click image for larger version. 

Name:	Access table to be appended.JPG 
Views:	21 
Size:	25.3 KB 
ID:	16667
    Click image for larger version. 

Name:	Excel import sheet.JPG 
Views:	21 
Size:	28.3 KB 
ID:	16668

  6. #21
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    rpeare is correct. I made a mistake when I provided the example code. The error is due to the quotes.

    EDIT:
    Try removing all of the data from the table you are importing to and then import.

    It seems there is an Index rule that is not allowing duplicates.

    also, like rpeare mentions below

    change
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, strTable, strFilePath, True

    to
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, strTable, strFilePath, False

  7. #22
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    What is the primary key of your table? it should be your ID field if it's something else you are likely duplicating the primary key/index, it also looks like you are importing the first row of your excel file which you can skip in your docmd.transferspreadsheet statement. You may also be trying to import a text value to a number field, for instance column I says 'date' but it's a text value, same for price and currency. The data type of the table has to match the data type of what you're importing.

  8. #23
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Quote Originally Posted by ItsMe View Post
    rpeare is correct. I made a mistake when I provided the example code. The error is due to the quotes.

    EDIT:
    Try removing all of the data from the table you are importing to and then import.

    It seems there is an Index rule that is not allowing duplicates.

    also, like rpeare mentions below

    change
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, strTable, strFilePath, True

    to
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, strTable, strFilePath, False

    Removed data
    Index = Id field
    Changed "True" to "False
    Deleted ID field from Excel
    Made sure all Excel columns had the same type (Text, Number, Date) as my columns in Access.


    Still getting this error message It won't go away
    Click image for larger version. 

Name:	Error.JPG 
Views:	15 
Size:	22.6 KB 
ID:	16679

  9. #24
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    PS, sorry it took me so long to respond today, my department decided to put me on optimizing photo quality today...that sucked

  10. #25
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Quote Originally Posted by rpeare View Post
    What is the primary key of your table? it should be your ID field if it's something else you are likely duplicating the primary key/index, it also looks like you are importing the first row of your excel file which you can skip in your docmd.transferspreadsheet statement. You may also be trying to import a text value to a number field, for instance column I says 'date' but it's a text value, same for price and currency. The data type of the table has to match the data type of what you're importing.

    Removed data
    Index = Id field
    Changed "True" to "False
    Deleted ID field from Excel
    Made sure all Excel columns had the same type (Text, Number, Date) as my columns in Access.


    Still getting this error message It won't go away
    Click image for larger version. 

Name:	Error.JPG 
Views:	15 
Size:	22.6 KB 
ID:	16680

  11. #26
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    put a field in your table tbl1_partpricingdataaggregation named F1, this error happens a lot when there is a column on your spreadsheet that looks blank but actually contains a value, I'm guessing if you link the excel spreadsheet you can see where the blank column is occurring (there may be more than 1) and they will be assigned a column name of FX where X is an incremental number change.

  12. #27
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Well, it looks like you're on to something. But I have no idea what your solution means or how to write code for it.
    Now I got this:
    Click image for larger version. 

Name:	Error.JPG 
Views:	15 
Size:	21.0 KB 
ID:	16681
    But on another note, my Excel has multiple sheets and at some point, I'll be linking all of the sheets to similar macros to the one we're creating right now.
    Is there a way to declare which sheet I'd like to be used from the Excel?

    Thanks so much for responding and offering your help and advice

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

  14. #29
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    I suggest you create a new table by using the wizard to import your spreadsheet. Then, use that table in your VBA.

  15. #30
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Quote Originally Posted by ItsMe View Post
    I suggest you create a new table by using the wizard to import your spreadsheet. Then, use that table in your VBA.

    Yeah, I tried importing the table I'd like to append and I'm still getting the F1 error

    But, I added 12 "FX+1" fields and it finally appended, but something very werid happened. See below, I'm not even sure how to explain it

    Click image for larger version. 

Name:	Error.JPG 
Views:	12 
Size:	39.6 KB 
ID:	16687

Page 2 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
  •  
Tech Forums: Microsoft Office Forums