Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    import from excel (Access 2000)

    Can you help me ? I need to update my ddu prices in the table products.I need to do it on a regular basis so i have to use some code.I receive however the new prices in an excel format.In my code i have to do the following:

    1.import the code and the ddu from the excel worksheet
    3. update the field code in the table products with the new value of the code from the excel.
    4. divide the value with 100,since the ddu in the excel is for 100 kg

    The excel sheet does not contain the productid, but only the code.However the field code is also present in the table.So i think this is not a problem for the updating
    I want to use the following code, that i have seen in the Lounge:
    Public Function ImportAndUpdate()
    Dim strFolder As String
    Dim strFile As String
    strFolder = "C:BEPricelist"
    strFile = Dir(strFolder & "*.xls")
    Do While strFile <> ""
    DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel97, _
    TableName:="products", _
    FileName:=strFolder & strFile, _
    HasFieldNames:=True, _
    Range:="ddu"
    strFile = Dir
    Loop


    Can i do this ? I am sending the table and the worksheet

  2. #2
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: import from excel (Access 2000)

    and here is the worksheet

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: import from excel (Access 2000)

    First of all, do you wish to override the products table everytime with the excel sheet?

    Which ever way you decide to go, you will need to import the sheet into a separate table and update/add from the import table to the products table. To update/add would take a couple of queries.

    I notice that you have Range:="ddu" as part of the import command, Access does not know what this means.


    Hope this helps.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: import from excel (Access 2000)

    Thank you for your reply.Yes, i wish to overrride the products table every time with the execl sheet.I understand i have to import the sheet into a separate table.I will call it Pricelist.However the code i have used somehow does not work.Furthermore i do not know what Range mean, but without that line the code shows red letters.How could i change the code ?

    Public Function ImportAndUpdate()
    Dim strFolder As String
    Dim strFile As String
    strFolder = "C:BEPricelist"
    strFile = Dir(strFolder & "*.xls")
    Do While strFile <> ""
    DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel97, _
    TableName:="pricelist", _
    FileName:=strFolder & strFile, _
    HasFieldNames:=True, _
    Range:="ddu"
    strFile = Dir
    Loop
    end function

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: import from excel (Access 2000)

    To get rid of the compile error change the 2 lines
    HasFieldNames:=True, _
    Range:="ddu"
    to
    HasFieldNames:=True

    What does your table PriceList contain in terms of fields? When you import a xls sheet with some columns having blank Access will substitute F2 as the column name if it is blank in the xls sheet.

    Th

  6. #6
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: import from excel (Access 2000)

    Thank you very much for your reply.I changed the line and now i get no errors at all, but somehowmy function doesnt work.I do not know why.Maybe i use not excel 97 but a higher version? In short when i click the button nothing happens.Shall i send the tables agan ?

    kind regards

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: import from excel (Access 2000)

    I tested your code, I only changed the value of strFolder to the folder containing Pricelist.xls on my PC. The worksheet was imported correctly into the database. The code does nothing with the products table - you still have to write that part. It only imports the Excel worksheet.

    Make sure that the value of strFolder is correct for your PC.

    Note: it doesn't matter whether you have a newer version of Excel. Excel 97, 2000, 2002 and 2003 all use the Excel 97 file format.

  8. #8
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: import from excel (Access 2000)

    Dear Hans,

    Thank you so much for your reply.I am very much concerned why i cannot get the results.I firmly believe in you and since you say so ,it must be true. Hence my concern.Nothing happens with me.I must have a deep misundestanding of the process.For example, i expect that the worksheet is converted into a table Pricelist.Is it so ? In order to avoid the possibility of an error with the path, i have put the excel sheet in the folder C:. I am sending again my database, could you plpease check it ? By the way, the form within this database is also your suggestion,i have taken it from somewhere in the Lounge.

    Kind regards

  9. #9
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: import from excel (Access 2000)

    and here is the excel sheet

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: import from excel (Access 2000)

    The function contains the line

    strFolder = "C:Pricelist"

    The code will look in the folder C:Pricelist. If your workbook is in C:, the code will not find it because it does not look in C: but in C:Pricelist. So you must either change the above line to

    strFolder = "C:"

    or you must create a folder C:Pricelist and store the workbook there.

    However, I now suspect that your code does not do what you intend. It now loops through *all* workbooks in the folder indicated by strFolder. I think you only want to import Pricelist.xls. You do not need to loop for that, you simply specify the full path of the workbook:

    Public Function Aral()
    Dim strFile As String
    strFile = "C:Pricelist.xls"
    DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel97, _
    TableName:="Pricelist", _
    FileName:=strFile, _
    HasFieldNames:=True
    End Function

  11. #11
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: import from excel (Access 2000)

    Many thanks ! It runs so smoothly now ! Thank you very much

    Pelio

  12. #12
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: import from excel (Access 2000)

    in the table Pricelist so made, the fields code and ddu are text, while i want them to be number.Can i add on something in my code to make them number ?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: import from excel (Access 2000)

    Create an empty table with the correct field types, and import into this table instead of into a new table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •