Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Lounger
    Join Date
    Jun 2009
    Location
    India
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    While trying idea from Prasad get a err message

    Invalid bracketing name 'xls.'. ( screenshot attached 'picture1')

    [attachment=84548:Picture1.jpg]

    further the query looks like the attached screenshot 'picture2'

    [attachment=84549:Picture2.jpg]

    where May09 is the name of xls file and BT is the sheet name.

    can any one help me?
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    There is an ` in the wrong place. It should be

    ...
    FROM`D:\Query\May09.xls`.`BT$` `BT$`

  3. #3
    New Lounger
    Join Date
    Jun 2009
    Location
    India
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    the query is generated through query wizard.
    ` is automaticaly placed.

    Even if i edit and place as suggested, the same error Invalid breackting of name 'xls.'.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Sorry, no idea then. It works for me with the syntax I posted.

  5. #5
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='782981' date='04-Jul-2009 12:06']Sorry, no idea then. It works for me with the syntax I posted.[/quote]
    I am working on this subject for many days & never faced the problem like this. Probably there is something wrong with your xls settings.
    Regards
    Prasad

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='prasad' post='782983' date='04-Jul-2009 14:12']I am working on this subject for many days & never faced the problem like this. Probably there is something wrong with your xls settings. [/quote]
    Was your reply directed at Budhrani?

  7. #7
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='782984' date='04-Jul-2009 12:13']Was your reply directed at Budhrani?[/quote]
    This was wrt query posted by Budhrani.
    Regards
    Prasad

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try omitting the extension .xls:

    Code:
    ...
    FROM `D:\Query\May09`.`BT$` `BT$`

  9. #9
    New Lounger
    Join Date
    Jun 2009
    Location
    India
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your efforts Hans.

    It was a problem with Excel itself.
    Afetr re-installing, it works fine.

  10. #10
    New Lounger
    Join Date
    Jun 2009
    Location
    India
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Problem moves one step forward.

    One table is added from apr09.xls. While adding one more table ( from other excel file i.e. may09.xls, with same structure ), it gives error

    "Couldn't access D:\query\May09."

    whereas it works fine, if I pick different sheets from a single xls file.

    what to do?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What is the SQL statement you're using?

  12. #12
    New Lounger
    Join Date
    Jun 2009
    Location
    India
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    it is through menu item:
    Table | Add Table | Workbook: D:\query\May09 | Add


    Even if I write SQL as:

    SELECT `sheet1$`.Code, `sheet1$`.Employee, `sheet1$`.at, `sheet1$`.Unit, `sheet1$`.Deptt, `sheet1$`.Month, `sheet1$`.GrossPaySlip, `sheet1$`.TakeHome
    FROM `D:\query\Apr09`.`sheet1$` `sheet1$`

    UNION ALL

    SELECT `sheet1$`.Code, `sheet1$`.Employee, `sheet1$`.at, `sheet1$`.Unit, `sheet1$`.Deptt, `sheet1$`.Month, `sheet1$`.GrossPaySlip, `sheet1$`.TakeHome
    FROM `D:\query\May09`.`sheet1$` `sheet1$`

    it takes the data from both xls, but all menu items under TABLE / CRITERIA / RECORD in microsoft query become disable.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    As soon as you explicitly mention the workbook in the SQL, you can't use the graphic tools for criteria etc. any more. You will have to edit the SQL manually to add tables, add criteria, sort the records etc.

  14. #14
    New Lounger
    Join Date
    Jun 2009
    Location
    India
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

  15. #15
    New Lounger
    Join Date
    Jun 2009
    Location
    India
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok!

    But my basic question remains un-answered.

    i.e.

    One table is added from apr09.xls. While adding one more table ( from other excel file i.e. may09.xls, with same structure ), it gives error
    "Couldn't access D:\query\May09."
    whereas it works fine, if I pick different sheets from a single xls file.

    it is through menu item:
    Table | Add Table | Workbook: D:\query\May09 | Add

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
  •