cannot create Excel QueryTable object with perl

Discussion in 'Perl Misc' started by robert.paschedag@web.de, Oct 12, 2005.

  1. Guest

    Hi,

    I'm trying to import some data into Excel with Perl. My Problem is,
    that I cannot create an object of a type "QueryTable"

    use strict;
    use Win32::OLE 'with';
    use Win32::OLE::Variant;

    my ($app, $obj, $book, $sheet, $file, $table);

    my $conn = 'TEXT;C:\\temp\\uni_source_target_language.csv';
    my $variant_conn = Variant(VT_BSTR, $conn);

    $app = Win32::OLE->new('Excel.Application', \&OLE_Quit) or die
    "oops\n";

    $book = $app->Workbooks->Add;
    $sheet = $book->ActiveSheet;
    $app->{Visible} = 1;

    $table = $sheet->QueryTables->Add(Connection => $variant_conn,
    Destination => $sheet->Range("A1"));

    exit(0);

    Subroutine .....

    The $table is always "undef". What am I doing wrong?

    pasche
     
    , Oct 12, 2005
    #1
    1. Advertising

  2. Matt Garrish Guest

    <> wrote in message
    news:...
    > Hi,
    >
    > I'm trying to import some data into Excel with Perl. My Problem is,
    > that I cannot create an object of a type "QueryTable"
    >
    > use strict;
    > use Win32::OLE 'with';
    > use Win32::OLE::Variant;
    >


    $Win32::OLE::Warn = 3;

    It should explain the problem.

    Matt
     
    Matt Garrish, Oct 13, 2005
    #2
    1. Advertising

  3. Matt Garrish Guest

    <> wrote in message
    news:...
    > Hi,
    >
    > I'm trying to import some data into Excel with Perl. My Problem is,
    > that I cannot create an object of a type "QueryTable"
    >
    > use strict;
    > use Win32::OLE 'with';
    > use Win32::OLE::Variant;
    >
    > my ($app, $obj, $book, $sheet, $file, $table);
    >
    > my $conn = 'TEXT;C:\\temp\\uni_source_target_language.csv';
    > my $variant_conn = Variant(VT_BSTR, $conn);
    >
    > $app = Win32::OLE->new('Excel.Application', \&OLE_Quit) or die
    > "oops\n";
    >
    > $book = $app->Workbooks->Add;
    > $sheet = $book->ActiveSheet;
    > $app->{Visible} = 1;
    >


    $table = $sheet->QueryTables->Add($variant_conn, $sheet->Range("A1"));

    Add takes a list, not a hash. See my other post for more info on how to help
    yourself by getting error messages.

    Matt
     
    Matt Garrish, Oct 13, 2005
    #3
  4. Guest

    Matt Garrish schrieb:

    > <> wrote in message
    > news:...
    > > Hi,
    > >
    > > I'm trying to import some data into Excel with Perl. My Problem is,
    > > that I cannot create an object of a type "QueryTable"
    > >
    > > use strict;
    > > use Win32::OLE 'with';
    > > use Win32::OLE::Variant;
    > >
    > > my ($app, $obj, $book, $sheet, $file, $table);
    > >
    > > my $conn = 'TEXT;C:\\temp\\uni_source_target_language.csv';
    > > my $variant_conn = Variant(VT_BSTR, $conn);
    > >
    > > $app = Win32::OLE->new('Excel.Application', \&OLE_Quit) or die
    > > "oops\n";
    > >
    > > $book = $app->Workbooks->Add;
    > > $sheet = $book->ActiveSheet;
    > > $app->{Visible} = 1;
    > >

    >
    > $table = $sheet->QueryTables->Add($variant_conn, $sheet->Range("A1"));
    >
    > Add takes a list, not a hash. See my other post for more info on how to help
    > yourself by getting error messages.
    >
    > Matt


    Hi Matt,

    thanks! Now I get an object, but now the "Refresh" Method does not seem
    to work. I do not see the imported data in the worksheet. Perhaps you
    can also help me here. Meanwhile I'll try my self.

    Pasche
     
    , Oct 13, 2005
    #4
  5. Guest

    Hi Matt,

    now it works. Thank you very much.

    Pasche
     
    , Oct 13, 2005
    #5
  6. Matt Garrish Guest

    <> wrote in message
    news:...
    >
    > Matt Garrish schrieb:
    >
    >> <> wrote in message
    >> news:...
    >> > Hi,
    >> >
    >> > I'm trying to import some data into Excel with Perl. My Problem is,
    >> > that I cannot create an object of a type "QueryTable"
    >> >
    >> > use strict;
    >> > use Win32::OLE 'with';
    >> > use Win32::OLE::Variant;
    >> >
    >> > my ($app, $obj, $book, $sheet, $file, $table);
    >> >
    >> > my $conn = 'TEXT;C:\\temp\\uni_source_target_language.csv';
    >> > my $variant_conn = Variant(VT_BSTR, $conn);
    >> >
    >> > $app = Win32::OLE->new('Excel.Application', \&OLE_Quit) or die
    >> > "oops\n";
    >> >
    >> > $book = $app->Workbooks->Add;
    >> > $sheet = $book->ActiveSheet;
    >> > $app->{Visible} = 1;
    >> >

    >>
    >> $table = $sheet->QueryTables->Add($variant_conn, $sheet->Range("A1"));
    >>
    >> Add takes a list, not a hash. See my other post for more info on how to
    >> help
    >> yourself by getting error messages.
    >>

    >
    > thanks! Now I get an object, but now the "Refresh" Method does not seem
    > to work. I do not see the imported data in the worksheet. Perhaps you
    > can also help me here. Meanwhile I'll try my self.
    >


    Please post another snippet of code displaying the problem (even if it's
    just another line or two). I won't be able to help unless I can see what
    you're doing.

    Matt
     
    Matt Garrish, Oct 13, 2005
    #6
  7. wrote:
    > Hi Matt,
    >
    > now it works. Thank you very much.


    What "works"? Please post some context so others can relate your success
    to a solution.

    --
    Josef Möllers (Pinguinpfleger bei FSC)
    If failure had no penalty success would not be a prize
    -- T. Pratchett
     
    Josef Moellers, Oct 13, 2005
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. George
    Replies:
    0
    Views:
    7,265
    George
    Jun 25, 2004
  2. Trevor Benedict R
    Replies:
    0
    Views:
    466
    Trevor Benedict R
    Jun 27, 2004
  3. Keith McDonald
    Replies:
    1
    Views:
    2,013
    Winista
    Aug 3, 2006
  4. Jona
    Replies:
    2
    Views:
    463
    Victor Bazarov
    Jul 6, 2007
  5. =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=

    Problem with Excel reports ::::Excel 2003 Migration To Excel 2007

    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=, Oct 5, 2007, in forum: ASP .Net
    Replies:
    15
    Views:
    1,548
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
Loading...

Share This Page