Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing an Excel range (2000)

    How can I import the C21:Z21 range from the attached Excel file into the Target field of the Consuntivo table in the attached mdb?

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

    Re: Importing an Excel range (2000)

    a) Is this going to be repeated frequently? In other words, do you want it to be automated?
    [img]/forums/images/smilies/cool.gif[/img] If so, should the date be determined automatically from the name of the workbook (or from cell E14 or E15), or will you enter the date manually?
    c) Would it be possible to obtain the Excel workbook in a format more suitable for import into Access?

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Importing an Excel range (2000)

    Hi,

    There are various ways to do this.

    I find that the easiest way without writing code would be to open your excel file and create a named range for the data. I am assuming that you want to import rows C19:Z19 and C21:Z21, Row C is a header ??. I highlighted from C19 to Z21 and created a named range.

    Open your access file and select File, Get external data, import, select excel as the data type and select the named range you need to import the data into your database. You could then use an append query to update the data. (I am not sure how you want the data appended.)

    Good Luck.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing an Excel range (2000)

    My <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> :
    In code use :
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TempTable", "C:8 LUGLIO 2004 MA.xls", False, "Recuperati_Foglio1!C21:Z21"
    to transfer the data in a temptable.
    Use Append queries or code to put the data in the table as you want.
    I don't understand that you have 24 cells and want to enter them in 1 field ? In different records ?
    Francois

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing an Excel range (2000)

    Francois,
    That's good advice, if I could just integrate it into this <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing an Excel range (2000)

    Hi Gary,
    I'm sorry but I need to automate the import procedure and I only need to import the C21:Z21 range, please see this.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing an Excel range (2000)

    Ciao Hans,
    a) Yes, automated, if possible.
    [img]/forums/images/smilies/cool.gif[/img] Yes, the date should be determined automatically from cell E15.
    c) I'm afraid a more suitable format should be obtained only through writing code that, e.g., would put the data in the C21:Z21 range in a vertical fashion so that it'd possibly be more easily imported in 24 records (starting where the Ora field=0) in the Target field of the Consuntivo table.
    I've prepared a macro that does that but how can you launch the following code from Access since the Excel file is downloaded from the internet every day and so doesn't contain the macro?

    Sub Macro1()
    Range("C21:Z21").Select
    Selection.Copy
    ActiveSheet.Paste
    Range("C23").Select
    Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
    , Transpose:=True
    End Sub

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

    Re: Importing an Excel range (2000)

    I would use cell E14, since it contains the date in your system language; you should be able to extract the date from it. I didn't write that part of the code, not having an Italian system.

    The attached text file contains code that runs Excel, opens a workbook and takes the data from C21:Z21. Although C19:Z19 contain 1, ..., 24, I assumed the values should correspond with Ora = 0, ..., 23. I put in a fixed date; as noted, you should write your own routine to determine the date.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing an Excel range (2000)

    Thank you Hans, it works perfectly.
    I'm using Excel 2003 and if the Excel file from which to get the data is from version 5.0, I get the pop up message:
    <font color=448800>Can't find the Show property for the Dialog class.</font color=448800>
    I'm trying to trap this error in the ErrHandler section of your routine so as to give a proper error message instead of the standard Access one but when I step through its code up to the line:
    <font color=red>If xlApp.Dialogs(xlDialogOpen).Show = False Then</font color=red>
    and proceed, everything frezees and I can't open the Immediate window to find the Err.Number.
    Do you know what the error number for this error is?

    By the way, I can't find xlApp.Dialogs(xlDialogOpen).Show anywhere else in your code, is it enough to just check for whether it's showing or not to actually open the File Open dialog box?

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

    Re: Importing an Excel range (2000)

    I don't understand why trying to open an Excel 5 workbook would cause the error message you mention. I can't reproduce this error.

    xlApp.Dialogs(xlDialogOpen).Show should display the File | Open dialog for Excel. If the user selects a file and clicks Open, the file is opened and the Show method returns True, and if the user clicks Cancel, the Show method returns False. So the line

    If xlApp.Dialogs(xlDialogOpen).Show = False Then

    displays the dialog and tests if the user actually opened a file.

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing an Excel range (2000)

    Hans, please try opening the attached Excel file.

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

    Re: Importing an Excel range (2000)

    When I try to open the workbook you attached directly in Excel (2002), I get a message that the workbook contains an invalid worksheet name and that this has been repaired. I cannot find out what the original worksheet name was, but it probably contained invalid characters - Excel 97 and later are more restrictive when it comes to naming worksheets. So you should repair the workbook in Excel before using it in Access. If I do that, I can open it in Access without problems.

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing an Excel range (2000)

    Problem is the end user sometimes forgets to convert the Excel 5.0 file into a more recent version so it'd be good if I could find the error number for when they try to import the data in Access.
    Do you get the error message,"Can't find the Show property for the Dialog class." when you launch the GetDataFromExcel procedure in the attached mdb feeding it the Excel 5.0 file(prova.xls)?

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

    Re: Importing an Excel range (2000)

    Yes, I do get the error message. The error number is easy to get: replace Err.Description in the error handling section by Err.Number temporarily. But this will only reveal that the error number is 1004, a general error number for errors generated by the application or object, so it could occur in other situations too. Still, you can handle error 1004 separately in the error handling section, using an If ... Then ... Else or Select Case statement.

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing an Excel range (2000)

    The Sub GetDataFromExcel() in Module1 of the attached mdb imports cell values from row 21 of the attached Excel file but the
    code stops at the line
    <font color=red> strSQL = "UPDATE Consuntivo SET Target = " & _
    Replace(xlWbk.Worksheets(1).Cells(21, i + 2), ",", ",") * 1000 & _
    " WHERE Giorno = #" & Format(datDate, "mm/dd/yyyy") & "# AND Ora = " & (i - 1) </font color=red>
    with a "Type mismatch" message if any cell on row 21 is empty instead of having zeroes in it.
    Is there any way to fill rows in the Target column of the Consuntivo table with zeroes or even leave them empty if the
    corresponding cells in the Excel file are empty?

Page 1 of 2 12 LastLast

Posting Permissions

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