Getting data from Google Docs into Excel

[bruce]

Google uses the 'Wire Protocol' to exchange data between gadgets,
Google Docs etc. Read here for an implementation of Wire Protocol for
Excel to get data from Google Docs into Excel.


As usual you can download all you need to implement this capability in
your own workbooks, and I look forward to your feedback and requests
for improvement.

http://ramblings.mcpher.com/Home/excelquirks/get-data-from-google-docs

[bruce]

Looks like Google have just tweaked their wire protocol a little to be
more jSON friendly - specifically the key/value pairs now have a
quoted keys - so for example what used to say table:[ now says "table":
[ .

I modified the cDataSet downloadable example file today that is the
subject of this article to work with this new Google wire format.

http://ramblings.mcpher.com/Home/excelquirks/downloadlist


bruce

[AL3537]

Excellent code- just what I was looking for! In testing it though I
noticed a strange behavior between operating systems; On Win XP (using
Excel 2007), it extracted the Google spreadsheet with the setting as
"private to me", however on Win 7 it wouldn't extract it unless the
document was set to publicly viewable (at first I though it might be
due to my using an evaluation copy of Office 2010 on that machine,
however when I ran it on my version of '07, the same error occurred
when the document was set to private- "did not find table definition
data").

Do you know of a security setting in Win 7 that would be causing such
behavior (the workbook is set as a macro enabled)?

[AL3537]

Forgot to add this to my last post:

Do you have any references on how to post information back to Google
docs- specifically, exporting a CSV version of the worksheet via VBA?

[bruce]

Hello

Welcome to the forum. If you haven't registered, please do to ensure
you get updates by email.

I haven't tried it on window 7 - but maybe this is more of an IE
version thing rather than windows 7, since it uses various IE
components. Which version of IE have you on each machine ?

Google Docs have plenty of import options (https://docs.google.com/
support/bin/answer.py?hl=en&answer=40608)

Is it something that cant be handled there that you are trying to do?

If you are trying to get data into google gadgets or charts, you can
read about that here http://ramblings.mcpher.com/Home/excelquirks/google-visualization


regards
Bruce

[bruce]

Just one further thing occurred to me.

As per a previous post, I noticed that google changed the wire
protocol subtly a week or two go. That means that the original
cDataSet.xlsm I released works on the previous version of the wire
protocol, and the latest one on the new.

Its possible that your issue is all a matter of timing of tests and
nothing to do with permissions and so on. You can check whether its
old or new protocol by entering the googledocs link to your data
into browser to see what gets returned. If the "table" is in quotes
it's the new one and will need the latest cDataSet.xlsm to work. The
previous wire protocol did not have quotes round its key values.

I'm going to tweak to support both in case it's a temporary google
thing, but haven't got round to it yet. Let me know if this solves
your problem

bruce

On Jul 29, 3:06 pm, bruce <br...@mcpher.com> wrote:
> Hello
>
> Welcome to the forum. If you haven't registered, please do to ensure
> you get updates by email.
>
> I haven't tried it on window 7 - but maybe this is more of an IE
> version thing rather than windows 7, since it uses various IE
> components. Which version of IE have you on each machine ?
>
> Google Docs have plenty of import options (https://docs.google.com/
> support/bin/answer.py?hl=en&answer=40608)
>
> Is it something that cant be handled there that you are trying to do?
>
> If you are trying to get data into google gadgets or charts, you can

> read about that herehttp://ramblings.mcpher.com/Home/excelquirks/google-visualization

[AL3537]

Thanks Bruce~

By the way, I've joined the group; my name is Allen and I'm currently
working as a developer for a small solutions company out of Arizona. I
appreciate your feedback, the resources will come in quite handy for a
project I'm starting on Monday. As development on it progresses (as
there are some CTQ requirements that extend the parameters of
importing and exporting to GD's), I'll be happy to share my work.

Have a great weekend!

[bruce]

Hi Allen

Good luck with the project. I would of course welcome any code or
article contributions or bug fixes you wish to make. Just post them
here or comment on the blog - http://excelramblings.blogspot.com/.

Also, if you are interested in open source development (javascript,
google gadget), i have a project here that i'm tweaking from time to
time that could do with some work - http://code.google.com/p/pepadagvroadmapper/

thanks

Bruce

[AL3537]

Hi Bruce,

Do you know of a way using VBA that a csv file can be uploaded to
Google Docs?

On Jul 29, 10:51 am, bruce <br...@mcpher.com> wrote:
> Hi Allen
>
> Good luck with the project. I would of course welcome any code or
> article contributions or bug fixes you wish to make. Just post them

> here or comment on the blog -http://excelramblings.blogspot.com/.

>
> Also, if you are interested in  open source development (javascript,
> google gadget), i have a project here that i'm tweaking from time to

> time that could do with some work -http://code.google.com/p/pepadagvroadmapper/

[bruce]

Hi Al

There's a few ways to get CSV into Google Docs from Excel. They would
usually be initiated from Docs though, as opposed to Excel. A simple
ways is of course to Use the Google Docs Import Csv menu option and
just import a statoc csv file.

Or you could use the =importData(Url) spreadsheet function in the
receiving Google docs sheet. This way you could write something in VBA
to post an updated Version of a CSV to a web site. Google Docs would
then import the updated version when opening.

There are other options involving writing google apps scripts, or a
modification of the approach I used for getting data into google
charts as below.

http://ramblings.mcpher.com/Home/excelquirks/google-visualization/general-purpose


Describe your problem and approach, and we'll figure out the best
solution.

Bruce

[AL3537]

Hi Bruce,

The problem involves one in which the client would like a macro
approach developed to upload an Excel worksheet to Google Docs. I've
created and tested the methods used for converting the range of cells
into a CSV format file and from there I'd like to pass it into the
method that would accomplish a file upload to Google Docs. I've
located some material that can be used for uploading files in VBA,
(http://www.motobit.com/tips/detpg_uploadvbaie/) however it's not
quite in line with where I'm going (it focuses on forms and converting
files to binary prior to uploading).

Allen

On Aug 7, 2:05 am, bruce <br...@mcpher.com> wrote:
> Hi Al
>
> There's a few ways to get CSV  into Google Docs from Excel. They would
> usually be initiated from Docs though, as opposed to Excel. A simple
> ways is of course to Use the Google Docs Import Csv menu option and
> just import a statoc csv file.
>
> Or you could use the =importData(Url) spreadsheet function in the
> receiving Google docs sheet. This way you could write something in VBA
> to post an updated Version of a CSV to a web site. Google Docs would
> then import the updated version when opening.
>
> There are other options involving writing google apps scripts, or a
> modification of the approach I used for getting data into google
> charts as below.
>

> http://ramblings.mcpher.com/Home/excelquirks/google-visualization/gen...

[bruce]

Hi Allen

Whereas it is possible to initiate this kind of thing using the google
API, - see the second part of this -
http://code.google.com/apis/spreadsheets/data/3.0/developers_guide.html#WorksheetFeeds

the complicated part is actually related to authorization (being
authenticated to be able to do it)
http://code.google.com/apis/spreadsheets/data/3.0/developers_guide.html#Auth

I'm not sure you should embark on all that - it will take a long time
if you haven't done something like it before.

However, I think you could achieve what you want using the
importData() cell formula.

Take a look at it and see if it can work for you. Go to a cell in a
google spreadsheet. And enter this into a cell =importdata("http://
www.sudokuworkplace.com/gettingstartedSeries.csv")

If that works for you, all you have to do is get your csv file to any
web site. The google sheet will update automatically.

Bruce

[AL3537]

Hi Bruce,

Question for you; in downloading from spreadsheets from Google, I'm
running into a bit of a problem- the spreadsheets I'm trying to gather
in are a bit elaborate (no formulas, just data entry- except its
structured fancifully with lots of gaps, merged cells and so forth).
In trying to restructure it a bit more utilitarian, I find I'm still
not able to import the spreadsheet deeper than two rows down.

The worksheet can be found at:
https://spreadsheets.google.com/spreadsheet/ccc?key=0AtpooRRcZnFRdGFHdFBxc2VzbjFoMm5icTNOaFZzZFE&hl=en_US#gid=0

In running some basic importation tests, I've found when I replace the
Constant URL in cDataSet.xlsm with (https://spreadsheets.google.com/
spreadsheet/tq?
range=A1:J6&key=0AtpooRRcZnFRdGFHdFBxc2VzbjFoMm5icTNOaFZzZFE&gid=0),
which is the entire range for the data, the macro hangs up and the
program dies.

However when I change the range= to A1:J2 in the Constant URL, the
macro easily pulls in the first two rows of the Google spreadsheet.

I've traversed the code in your program, but I'm still not seeing the
"forest for the trees" in where the problem lies.

Thanks for all of your help- it's been invaluable!

Allen

On Aug 8, 3:44 am, bruce <br...@mcpher.com> wrote:
> Hi Allen
>

> Whereas it is possible to initiate this kind of thing using the google

> API, - see the second part of this -http://code.google.com/apis/spreadsheets/data/3.0/developers_guide.ht...

>
> the complicated part is actually related to authorization (being

> authenticated to be able to do it)http://code.google.com/apis/spreadsheets/data/3.0/developers_guide.ht...

>
> I'm not sure you should embark on all that - it will take a long time
> if you haven't done something like it before.
>
> However, I think you could achieve what you want using the
> importData() cell formula.
>
> Take a look at it and see if it can work for you. Go to a cell in a

> google spreadsheet. And enter this into a cell =importdata("http://www.sudokuworkplace.com/gettingstartedSeries.csv")

[AL3537]

By the way, I'm using the table gadget to generate the url.

On Aug 9, 8:14 pm, AL3537 <Adwatts3...@cox.net> wrote:
> Hi Bruce,
>

> Question for you; in downloading from spreadsheets from Google, I'm
> running into a bit of a problem- the spreadsheets I'm trying to gather
> in are a bit elaborate (no formulas, just data entry- except its
> structured fancifully with lots of gaps, merged cells and so forth).
> In trying to restructure it a bit more utilitarian, I find I'm still
> not able to import the spreadsheet deeper than two rows down.
>

> The worksheet can be found at:https://spreadsheets.google.com/spreadsheet/ccc?key=0AtpooRRcZnFRdGFH...

[bruce]

Hi Allen

I checked it out and noticed that the cell style was being sent over
google wire. I never had that before, so it may be new. for
example.... ( "p":{"style":"background-color: rgb(255, 255, 255);"} ).
I haven't implemented duplicating the style yet (its kind of boring,
but I will get around to it shortly) ). This shouldn't have been a
problem as it is designed to ignore stuff not yet implemented, but
there was a bug that showed up parsing the unexpected data.

Anyway , it was a simple fix incorporated in the new version of
cDataSet.xlsm which you can now download. I'll post a comment here
when i get round to carrying over styles for cell formatting.

Thanks for noticing!

bruce

[AL3537]

Thanks as always Bruce!

I know, we’ve brought it to the attention of our client (the document
I used in the example was fictitious and developed on my own doc site-
the structure was the primary focus for the test) as all of her
documents in the cloud are publicly viewable and contain PII within
them- she isn’t concerned though??!? (we are encrypting the work
however)

One last question for you- I downloaded the newest version of cDataSet
and it worked great for pulling down the sheet I had problems with (a
tip of the hat once more)! Just for kicks I tried to download the
clients sheet (which extends southward 105 rows); it immediately
ceases with a run time error (28)- Out of Stack Space. Is there a
variable within the code that I can change that would allow for the
program to pull in the entire sheet (the debug starts at
the .populateGoogleWire sWire, Range("Clone!$a$1") line in the
GoogleWireExample module)? Is there perhaps an absolute maximum range
of cells that can be brought in (if so, how many- just in case
redesigning their cloud spreadsheets is necessary)?

Have a great day!
Allen

> ...
>
> read more »

[bruce]

Allen

thanks for exercising the module again. Too much recursion enthusiasm
caused the problem. I should have read my own article duh..
http://ramblings.mcpher.com/Home/excelquirks/recursionlink/getting-to-grips-with-recursion

Anyway, trivial mods , latest non recursive version loaded , should
now cope with whatever can be sent over http. I dont have anything to
test it with so let me know how it goes

bruce

> ...
>
> read more »

[AL3537]

Bruce

Worked like a charm! It looks like I'm still going to have to reformat
the clients data input though- change it from vertical data entry to
linear. As soon as I'm finished with that portion of the job, I'm
going to send you a generic (non client) version of the workbook- I've
implemented a static array that loops through x amount of Google
workbooks looking for y amount of worksheets in each one. From there
it checks for and eliminates duplicate worksheets and then eliminates
all empty rows between the imported worksheets.

It looks like I have to switch gears on that phase for a day or two-
new need has (scope) crept up- using a combo box to look up
demographic data and populate their charts and graphs.

ah it never ends!

Allen

On Aug 10, 3:48 pm, bruce <br...@mcpher.com> wrote:
> Allen
>
> thanks for exercising  the module again. Too much recursion enthusiasm

> caused the problem. I should have read my own article  duh..http://ramblings.mcpher.com/Home/excelquirks/recursionlink/getting-to...

> ...
>
> read more »

[AL3537]

Hi Bruce,

Question for you; I'm trying to extract all the information from a
table based on criteria from a column within that table. I've devised
the code and am in the process of a proof of concept before hard
coding it into the clients program. When I run the macro however, I'm
receiving an error message (runtime error 1004; SQL syntax error) on
the .Refresh BackgroundQuery:=False line. Below is the module code and
I'll email you the test program rather than explain in great detail
the column headings and so forth:

<code>Sub kTest()
Dim FilePath As String, FileName As String
Dim FullName As String, strSQL As String
Dim Cnn As String, ContType As String

ContType = Application.InputBox("Enter the Type", "Type")
If ContType = "" Then Exit Sub

FilePath = ThisWorkbook.Path
FileName = ThisWorkbook.Name
FullName = FilePath & "\" & Left(FileName, Len(FileName) - 4)

Cnn = "ODBC;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName &
";DefaultDir=" & FilePath & _
",;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

strSQL = "SELECT `Sheet1$`.Type, `Sheet1$`.Region, `Sheet1$`.Contact,"
strSQL = strSQL & "`Sheet1$`.Phone, `Sheet1$`.Email, `Sheet1$`.ADD1,"
strSQL = strSQL & "`Sheet1$`.ADD2, `Sheet1$`.ADD3 "
strSQL = strSQL & "FROM `" & FullName & "`.`Sheet1$` `Sheet1$` "
strSQL = strSQL & "WHERE `Sheet1$`.Type='" & ContType & "' "
'strSQL = strSQL & "ORDER BY `Sheet1$`.Type, `Sheet1$`.Region"
strSQL = strSQL & "ORDER BY `Sheet1$`.Service Type, `Sheet1$`.Region"


[sheet2!a1].CurrentRegion.Clear 'change sheet name
With [Sheet2].QueryTables.Add(Connection:=Cnn, Destination:=[sheet2!
a1], _
Sql:=strSQL)
'Error coming in on this line
.Refresh BackgroundQuery:=False
.RefreshStyle = xlOverwriteCells
End With
End Sub</code>

> ...
>
> read more »

[bruce]

Hi Allen

Your syntax problem was with the sqlstring - there was nothing wrong
with the line it was complaining about.

I've tidied it up a little - try this

Option Explicit
Sub kTest()
Dim strSQL As String, cnn As String, ContType As String

ContType = Application.InputBox("Enter the Type", "Type")
If ContType = "" Then Exit Sub

cnn = "ODBC;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName &
";DefaultDir=" _
& ThisWorkbook.Path & _
";DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

strSQL = "select * from [sheet1$]"
strSQL = strSQL & " where [sheet1$].type = '" & ContType & "'"
strSQL = strSQL & " order by [sheet1$].type, [sheet1$].region"

[sheet2!a1].CurrentRegion.Clear 'change sheet name

With [Sheet2].QueryTables.Add(Connection:=cnn,
Destination:=[sheet2!a1], _
Sql:=strSQL)
.Refresh False
.Delete
End With

End Sub

> ...
>
> read more »

[Allen Watts]

Thanks Bruce,

Waking up, my thoughts were that the strSQL was the culprit; I've used SQL
in Access, however using it in Excel is new to me (but very cool- didn't
realize how flexible Excel was!). Before seeing your email, I thought that
it had something to do with it not recognizing sheet1's info not being a
table- thanks again for your sage advice!

Allen

Hi Allen

End Sub

--
You received this message because you are subscribed to the Google Groups
"Excel Ramblings" group.
To post to this group, send email to excel-r...@googlegroups.com.
To unsubscribe from this group, send email to
excel-ramblin...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/excel-ramblings?hl=en.

[AL3537]

Hi Bruce~

Here's a strange one; the "proof of concept" worked great in the test
worksheet, however when I implemented the same code (revised of course
to match the workbook), I'm getting an object required run-time error
on the "landing zone" worksheet for the data I'm attempting to
extrapolate (brief synopsis; the workbook is HUGE- the error has
occurred in its original format as well as its pared down version {in
which I've eliminated all unnecessary sheets except for the data
origin and data destination- homogenized- I've sent you the copy}.

Basically, using the same format (input through a message box), the
user enters an actual region (such as New York) and the code should
pull from the data origin (in this case, AlumniDashboard) all records
matching the criteria and place it in a sheet named Data. When I run
the macro, the error occurs on the line:
With [Data].QueryTables.Add(Connection:=cnn, Destination:=[Data!a1],
Sql:=strSQL) the entire code is posted below.

Going over that line, the Connection (cnn) and Sql shows up, however
the Destination does not- the code itself is a duplicate of the
revised one you posted, however I don't understand why it's not
recognizing the sheet name as the destination object?? (I even tried
replacing the reference with the sheet # as indicated by the MS Excel
Object reference to no avail).

I'll email you the worksheet with the misfiring error.

Thanks as always!

Allen

PS- the code in the macro:

Option Explicit
Sub kTest()
Dim strSQL As String, cnn As String, ContType As String

ContType = Application.InputBox("Enter the Region", "Region")

If ContType = "" Then Exit Sub

cnn = "ODBC;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName &
";DefaultDir=" _
& ThisWorkbook.Path & _
";DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

strSQL = "select * from [AlumniDashboard$]"
strSQL = strSQL & " where [AlumniDashboard$].corps_region = '" &
ContType & "'"
strSQL = strSQL & " order by [AlumniDashboard$].corps_region,
[AlumniDashboard$].lastname"

[Data!a1].CurrentRegion.Clear
With [Data].QueryTables.Add(Connection:=cnn, Destination:=[Data!
a1], Sql:=strSQL)

.Refresh False
.Delete
End With
End Sub

> ...
>
> read more »

[bruce]

Allen

I'm on way to airport so cant download this right now. However at
first glance, I seem to remember some problem or bug with renamed
sheets. I've never been a fan of the [data] type syntax anyway
actually. I'm guessing if you replace [data] with a new created
[sheet1] everything will work . i know it sounds stupid)

.why not select the worksheet and work on the object instead
dim ws as worksheet
set ws = worksheets("data")
.
.
.
ws.cells.clear
With ws.QueryTables.Add(Connection:=cnn, Destination:=ws.cells,
Sql:=strSQL)
.
.
.

once you've done that, break down the sql into smaller chunks (without
order by for example) and check that each bit works

btw .. with lots of record 000 of thousands, and a high hit rate on
the sql, many machines wont be able to cope or anyway it will take an
unnacceptably long time. Anyway, I'll have a look at this probably
tomorrow

Bruce

> ...
>
> read more »

[AL3537]

Hi Bruce~

How funny, right before your message came in I toyed with the idea
that the program was barfing on the sheet reference; changing the With
[Data] line to the sheet# moved its complaining right past that line
(to the .Refresh False line- which was due to the number of columns-
260 and records- 15000+, Excel has its limitations!).

After tooling around with it, I pulled in a couple columns based on
the region- worked great (albeit slowly), adding a few more columns-
no problem. The test will be tomorrow though- the client wants to add
at least 40-50 more(??!??!- crossing my fingers).

The functionality turned out pretty cool- the user will select from a
combo-box a region (based on a specific column's data within the
origin table which I manually created a named list for)- I also added
two additional items within the list- a blank entry which exits the
sub-routine (combobox change) and a NOT IN LIST entry- which opens up
a message box allowing the user to enter in a region (that may have
been entered since the named list was created). based on the select
case of the cb's index, the query runs and populates an adjacent
sheet.

Once again, thanks for you insight- hope your trip went well!

Allen

> ...
>
> read more »

[bruce mcpherson]

Hi Allen

Hi Allen

Glad you got it going.. I think you may need to revisit though if this is supposed to be interactive - Excel quickly goes down the toilet with a lot of data. Let me know how it works out.

I'm in US for the week actually - going back to UK on saturday

bruce

[AL3537]

Hi Bruce,

Hope you're stay here is going well and you didn't get groped too
invasably (is this a word??) by the TSA (lol).

update; the query worked without issue (at least the initial one- via
the one originating sheet). Of course, the client didn't specify that
more information was needed (based on other worksheets within the
workbook). Rather than add more SQL garble to the on-change event of
the combo box, I chose instead to call additional functions after the
initial query was put in place- worked like a charm! (example code
below)


<code>
Public Sub ComboBox1_Change()

'On change event of the ComboBox initiates the first query for
regional information based on the alumni dashboard
'worksheet. After completing the first query, the procedure hands off
to the next function which runs the second
'query and so on. We pass in the ContType as an argument to preceeding
function.

'variables

Dim strSQL As String, cnn As String, ContType As String

'set connection string variable

cnn = "ODBC;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName &
";DefaultDir=" _
& ThisWorkbook.Path & _
";DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

'set ContType variable to city based on index selected; add one
last entry
'in city list of user defined- open up message box and use that
input for the lookup variable
Select Case ComboBox1.ListIndex
Case 0: Exit Sub
Case 1: ContType = "Alabama"
Case 2: ContType = "Austin"
Case 3: ContType = "Baltimore"
Case 4: ContType = "Bay Area"
Case 5: ContType = "Charlotte"
Case 6: ContType = "Chicago"
Case 7: ContType = "Colorado"
Case 8: ContType = "Connecticut"
Case 9: ContType = "D.C. Region"
Case 10: ContType = "Dallas-Fort Worth"
Case 11: ContType = "Detroit"
Case 12: ContType = "Eastern North Carolina"
Case 13: ContType = "Greater Boston"
Case 14: ContType = "Greater Nashville"
Case 15: ContType = "Greater New Orleans"
Case 16: ContType = "Greater Newark"
Case 17: ContType = "Hawaii"
Case 18: ContType = "Houston"
Case 19: ContType = "Indianapolis"
Case 20: ContType = "Jacksonville"
Case 21: ContType = "Kansas City"
Case 22: ContType = "Las Vegas Valley"
Case 23: ContType = "Los Angeles"
Case 24: ContType = "Memphis"
Case 25: ContType = "Metro Atlanta"
Case 26: ContType = "Miami-Dade"
Case 27: ContType = "Mid-Atlantic"
Case 28: ContType = "Milwaukee"
Case 29: ContType = "Mississippi Delta"
Case 30: ContType = "New Mexico"
Case 31: ContType = "New York"
Case 32: ContType = "Oklahoma"
Case 33: ContType = "Phoenix"
Case 34: ContType = "Rhode Island"
Case 35: ContType = "Rio Grande Valley"
Case 36: ContType = "San Antonio"
Case 37: ContType = "South Dakota"
Case 38: ContType = "South Louisiana"
Case 39: ContType = "St. Louis"
Case 40: ContType = "Twin Cities"
Case 41: ContType = Application.InputBox("Enter the Region's
Name:", "User-Defined Region")
End Select

'Sql statements for the first query
strSQL = "select [Alumni Dashboard$].person_id, [Alumni Dashboard
$].firstname, [Alumni Dashboard$].lastname,"
strSQL = strSQL & "[Alumni Dashboard$].corps_last_name, [Alumni
Dashboard$].corps_region, [Alumni Dashboard$].alum_region,"
strSQL = strSQL & "[Alumni Dashboard$].alum_region_override,
[Alumni Dashboard$].person_type, [Alumni Dashboard$].corps_year,"
strSQL = strSQL & "[Alumni Dashboard$].poc, [Alumni Dashboard
$].gender, [Alumni Dashboard$].ethnicity,"
strSQL = strSQL & "[Alumni Dashboard$].email, [Alumni Dashboard
$].address1, [Alumni Dashboard$].address2,"
strSQL = strSQL & "[Alumni Dashboard$].city, [Alumni Dashboard
$].state, [Alumni Dashboard$].zip,"
strSQL = strSQL & "[Alumni Dashboard$].homephone, [Alumni Dashboard
$].mobile, [Alumni Dashboard$].workphone,"
strSQL = strSQL & "[Alumni Dashboard$].empl_profession, [Alumni
Dashboard$].empl_role, [Alumni Dashboard$].empl_field,"
strSQL = strSQL & "[Alumni Dashboard$].empl_level, [Alumni
Dashboard$].empl_title, [Alumni Dashboard$].empl_employer,"
strSQL = strSQL & "[Alumni Dashboard$].empl_school_or_division,
[Alumni Dashboard$].empl_school_type, [Alumni Dashboard
$].empl_placement_status,"
strSQL = strSQL & "[Alumni Dashboard$].empl_start, [Alumni
Dashboard$].Graduate_Degree, [Alumni Dashboard
$].Graduate_Field_Of_Study,"
strSQL = strSQL & "[Alumni Dashboard$].University, [Alumni
Dashboard$].pli_active_pipeline, [Alumni Dashboard
$].pli_declared_this_fy,"
strSQL = strSQL & "[Alumni Dashboard$].pli_current_declared,
[Alumni Dashboard$].pli_current_elected, [Alumni Dashboard
$].pli_past_elected,"
strSQL = strSQL & "[Alumni Dashboard
$].pli_past_unsuccessful_candidate, [Alumni Dashboard
$].pli_departure_flag, [Alumni Dashboard$].pli_pipeline_status,"
strSQL = strSQL & "[Alumni Dashboard$].pli_race_result, [Alumni
Dashboard$].pli_candidate_office, [Alumni Dashboard
$].pli_filing_deadline,"
strSQL = strSQL & "[Alumni Dashboard$].pli_declared_date, [Alumni
Dashboard$].pli_election_date, [Alumni Dashboard$].pli_office_status,"
strSQL = strSQL & "[Alumni Dashboard$].pli_office_start_date,
[Alumni Dashboard$].pli_office_name, [Alumni Dashboard
$].pli_office_type,"
strSQL = strSQL & "[Alumni Dashboard$].pli_departure_status,
[Alumni Dashboard$].running_for_office_interest, [Alumni Dashboard
$].interest_timeframe,"
strSQL = strSQL & "[Alumni Dashboard$].pli_campaign_trainings,
[Alumni Dashboard$].political_activist, [Alumni Dashboard
$].policy_interest,"
strSQL = strSQL & "[Alumni Dashboard$].pli_lee_mem, [Alumni
Dashboard$].pli_lee_join_date, [Alumni Dashboard
$].pli_webinar_registrant,"
strSQL = strSQL & "[Alumni Dashboard$].pli_webinar_attendee,
[Alumni Dashboard$].pli_newsblast_subsc, [Alumni Dashboard
$].pli_num_events,"
strSQL = strSQL & "[Alumni Dashboard$].pli_fellowships, [Alumni
Dashboard$].pli_fellowships_incomplete, [Alumni Dashboard
$].pli_int_pts,"
strSQL = strSQL & "[Alumni Dashboard$].pli_int_pts_cmts, [Alumni
Dashboard$].pli_exp_pts, [Alumni Dashboard$].pli_exp_pts_cmts,"
strSQL = strSQL & "[Alumni Dashboard$].leadership_interest,
[Alumni Dashboard$].leadership_interest_level, [Alumni Dashboard
$].current_year_money,"
strSQL = strSQL & "[Alumni Dashboard
$].current_year_qualified_time, [Alumni Dashboard$].current_year_time,
[Alumni Dashboard$].fy10_money,"
strSQL = strSQL & "[Alumni Dashboard$].Willing_to_share_info,
[Alumni Dashboard$].LEE_member, [Alumni Dashboard
$].Not_member_and_willing_to_share,"
strSQL = strSQL & "[Alumni Dashboard$].Declared from [Alumni
Dashboard$]"
strSQL = strSQL & " where [Alumni Dashboard$].alum_region = '" &
ContType & "'"
strSQL = strSQL & " order by [Alumni Dashboard$].alum_region,
[Alumni Dashboard$].lastname"

'Place the extracted information on the Region sheet; clear out
any information that was there first and
'populate it with the data from the SQL statement
[regional!a1].CurrentRegion.Clear
With [Sheet20].QueryTables.Add(Connection:=cnn,
Destination:=[regional!a1], Sql:=strSQL)

.Refresh False
.Delete
End With

'call the function to run the second query
getDataOne (ContType)

'call the function to run the third query
getDataTwo (ContType)

'call the function to run the fourth query
getDataThree (ContType)

'call the function to run the fourth query
getDataFour (ContType)

'call the function to run the fourth query
getDataFive (ContType)

End Sub

Public Function getDataOne(ContType As String)

'Function that runs the region query against the Group Meeting sheet;
we pass
'in the combo box selection as a string variable based on the users
selection

'variables

Dim strSQL As String, cnn As String

'set up the connection string

cnn = "ODBC;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName &
";DefaultDir=" _
& ThisWorkbook.Path & _
";DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

'sql statements for second query
strSQL = "select [Group Meetings$].alum_region, [Group Meetings
$].personid, [Group Meetings$].firstname,"
strSQL = strSQL & "[Group Meetings$].lastname, [Group Meetings
$].corps_year, [Group Meetings$].account,"
strSQL = strSQL & "[Group Meetings$].startdate, [Group Meetings
$].description, [Group Meetings$].meeting_topics,"
strSQL = strSQL & "[Group Meetings$].politics_policy, [Group
Meetings$].notes, [Group Meetings
$].active_talent_recruitment_activity,"
strSQL = strSQL & "[Group Meetings$].userid from [Group Meetings
$]"
strSQL = strSQL & " where [Group Meetings$].alum_region = '" &
ContType & "'"
strSQL = strSQL & " order by [Group Meetings$].alum_region, [Group
Meetings$].lastname"

'place the results in Regional2 worksheet
[regional2!a1].CurrentRegion.Clear
With [Sheet21].QueryTables.Add(Connection:=cnn,
Destination:=[regional2!a1], Sql:=strSQL)

.Refresh False
.Delete
End With

End Function

Public Function getDataTwo(ContType As String)

'Function that runs the region query against the Training sheet; we
pass
'in the combo box selection as a string variable based on the users
selection

'variables

Dim strSQL As String, cnn As String

'set up the connection string

cnn = "ODBC;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName &
";DefaultDir=" _
& ThisWorkbook.Path & _
";DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

'sql statements for third query
strSQL = "select [Trainings$].alum_region, [Trainings$].personid,
[Trainings$].training_regions,"
strSQL = strSQL & "[Trainings$].firstname, [Trainings$].lastname,
[Trainings$].program_one,"
strSQL = strSQL & "[Trainings$].rock, [Trainings$].m_f, [Trainings
$].poc,"
strSQL = strSQL & "[Trainings$].ethnicity, [Trainings
$].program_start, [Trainings$].program_end,"
strSQL = strSQL & "[Trainings$].training_fellowship, [Trainings
$].training,"
strSQL = strSQL & "[Trainings$].type_of_training from [Trainings
$]"
strSQL = strSQL & " where [Trainings$].alum_region = '" & ContType
& "'"
strSQL = strSQL & " order by [Trainings$].alum_region, [Trainings
$].lastname"

'place the results in Regional3 worksheet
[regional3!a1].CurrentRegion.Clear
With [Sheet22].QueryTables.Add(Connection:=cnn,
Destination:=[regional3!a1], Sql:=strSQL)

.Refresh False
.Delete
End With

End Function

Public Function getDataThree(ContType As String)

'Function that runs the region query against the Fellowship sheet; we
pass
'in the combo box selection as a string variable based on the users
selection

'variables

Dim strSQL As String, cnn As String

'set up the connection string

cnn = "ODBC;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName &
";DefaultDir=" _
& ThisWorkbook.Path & _
";DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

'sql statements for fourth query
strSQL = "select [Fellowship$].region, [Fellowship$].regions,
[Fellowship$].personid,"
strSQL = strSQL & "[Fellowship$].firstname, [Fellowship
$].lastname, [Fellowship$].program_one,"
strSQL = strSQL & "[Fellowship$].LEE_fellowship, [Fellowship
$].m_f, [Fellowship$].poc,"
strSQL = strSQL & "[Fellowship$].ethnicity, [Fellowship
$].program_start, [Fellowship$].program_end,"
strSQL = strSQL & "[Fellowship$].training_fellowship, [Fellowship
$].early_stage, [Fellowship$].mid_career,"
strSQL = strSQL & "[Fellowship$].advanced from [Fellowship$]"
strSQL = strSQL & " where [Fellowship$].region = '" & ContType &
"'"
strSQL = strSQL & " order by [Fellowship$].region, [Fellowship
$].lastname"

'place the results in Regional4 worksheet
[regional4!a1].CurrentRegion.Clear
With [Sheet23].QueryTables.Add(Connection:=cnn,
Destination:=[regional4!a1], Sql:=strSQL)

.Refresh False
.Delete
End With

End Function

Public Function getDataFour(ContType As String)

'Function that runs the region query against the LEE Watchlist sheet;
we pass
'in the combo box selection as a string variable based on the users
selection

'variables

Dim strSQL As String, cnn As String

'set up the connection string

cnn = "ODBC;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName &
";DefaultDir=" _
& ThisWorkbook.Path & _
";DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

'sql statements for fifth query
strSQL = "select [LEE Watchlist$].alum_region, [LEE Watchlist
$].pid, [LEE Watchlist$].firstname,"
strSQL = strSQL & "[LEE Watchlist$].lastname, [LEE Watchlist
$].poc_within_LEE_staff, [LEE Watchlist$].database_pipeline_status,"
strSQL = strSQL & "[LEE Watchlist$].actual_pipeline_status, [LEE
Watchlist$].optional_potential_seat_office, [LEE Watchlist
$].declared_intent,"
strSQL = strSQL & "[LEE Watchlist$].candidate_filed, [LEE Watchlist
$].filing_date, [LEE Watchlist$].election_date,"
strSQL = strSQL & "[LEE Watchlist$].fundraising_target, [LEE
Watchlist$].money_raised, [LEE Watchlist$].cash_on_hand,"
strSQL = strSQL & "[LEE Watchlist$].individual_contribution_limit,
[LEE Watchlist$].no_of_vol_supporters_working_for_candidate,"
strSQL = strSQL & "[LEE Watchlist$].google_doc_link from [LEE
Watchlist$]"
strSQL = strSQL & " where [LEE Watchlist$].alum_region = '" &
ContType & "'"
strSQL = strSQL & " order by [LEE Watchlist$].alum_region, [LEE
Watchlist$].lastname"

'place the results in Regional5 worksheet
[regional5!a1].CurrentRegion.Clear
With [Sheet24].QueryTables.Add(Connection:=cnn,
Destination:=[regional5!a1], Sql:=strSQL)

.Refresh False
.Delete
End With

End Function

Public Function getDataFive(ContType As String)

'Function that runs the region query against the PALI Dashboard sheet;
we pass
'in the combo box selection as a string variable based on the users
selection

'variables

Dim strSQL As String, cnn As String

'set up the connection string

cnn = "ODBC;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName &
";DefaultDir=" _
& ThisWorkbook.Path & _
";DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

'sql statements for sixth query
strSQL = "select [PALI Dashboard$].alum_region, [PALI Dashboard
$].personid, [PALI Dashboard$].firstname,"
strSQL = strSQL & "[PALI Dashboard$].lastname, [PALI Dashboard
$].cy, [PALI Dashboard$].cr,"
strSQL = strSQL & "[PALI Dashboard$].ethnicity, [PALI Dashboard
$].poc, [PALI Dashboard$].email,"
strSQL = strSQL & "[PALI Dashboard$].profession, [PALI Dashboard
$].role, [PALI Dashboard$].field,"
strSQL = strSQL & "[PALI Dashboard$].employer, [PALI Dashboard
$].title, [PALI Dashboard$].pre_survey_exp_bucket_021511, [PALI
Dashboard$].bucket_final,"
strSQL = strSQL & "[PALI Dashboard
$].policy_interest_from_alumni_survey, [PALI Dashboard
$].comm_org_interest_from_alumni_survey,"
strSQL = strSQL & "[PALI Dashboard$].type_of_leader, [PALI
Dashboard$].b2_b3_bx_by_and_actively_pursuing, [PALI Dashboard
$].b3_and_p,"
strSQL = strSQL & "[PALI Dashboard$].b3_and_a_or_o from [PALI
Dashboard$]"
strSQL = strSQL & " where [PALI Dashboard$].alum_region = '" &
ContType & "'"
strSQL = strSQL & " order by [PALI Dashboard$].alum_region, [PALI
Dashboard$].lastname"

'place the results in Regional6 worksheet
[regional6!a1].CurrentRegion.Clear
With [Sheet25].QueryTables.Add(Connection:=cnn,
Destination:=[regional6!a1], Sql:=strSQL)

.Refresh False
.Delete
End With

End Function
</code>

Originally, I tested trying to run the code within the same on change
event, but variable issues caused problems so I broke it into seperate
modules and the queries ran fine.
~Allen

On Aug 15, 9:08 pm, bruce mcpherson <br...@mcpher.com> wrote:
> Hi Allen
>
> Hi Allen
>
> Glad you got it going.. I think you may need to revisit though if this is
> supposed to be interactive - Excel quickly goes down the toilet with a lot
> of data. Let me know how it works out.
>
> I'm in US for the week actually - going back to UK on saturday
>
> bruce
>

> ...
>
> read more »

[AL3537]

Hi Bruce~

Moving back to getting Google Docs into Excel:

I'm back to working on that portion of the project but am running into
a problem; I have the new cDataSet version, but when I implement the
googleWireExample code as an array (of 3 Google docs) and loop through
them (I've set up an appropriate offset to accommodate all three
Google spreadsheets fitting without overlap). The only problem is,
only the third sheet remains after the macro has run (the two previous
sheets appear initially, but then are cleared out- the third sheet is
placed where it should, on row 199- as I've put in an offset of 99).
I've played with the getLikelyColumns sub in cDataSets to attempt a
logical fix, to no avail- I've since reset it back to its "factory"
defaults. Here's the code I've implemented for googleWireExample:


Option Explicit
Option Base 1

Public Sub getGoogleDocs()

'Create an array object and initialize it; give it the correct
'number of objects in the clients program
Dim documents(3) As String
Dim doc As Variant

'Array objects
documents(1) = "https://docs.google.com/spreadsheet/tq?
range=A1:K98&key=0AtpooRRcZnFRdENvUXVXWXVSX3h4c2JSR0s0RWpnRnc&gid=0"
documents(2) = "https://docs.google.com/spreadsheet/tq?
range=A1:K98&key=0AtpooRRcZnFRdENvUXVXWXVSX3h4c2JSR0s0RWpnRnc&gid=1"
documents(3) = "https://docs.google.com/spreadsheet/tq?
range=A1:K98&key=0AtpooRRcZnFRdENvUXVXWXVSX3h4c2JSR0s0RWpnRnc&gid=2"

'Initialize the cell range where the information will be printed;
'change the function getLikelyColumnRange(Optional ws As Worksheet
= "Clone")
'to the right worksheet name in the clients program
Dim rng As Range
'Start in cell a1
Set rng = Range("a1") 'I've used this set and the one below it-
(function getLikelyColumnRange has ws set as "Clone")
'Set rng = Range("Clone!$a$1")
rng.Select

'variable used for array reference
Dim i As Integer
i = 1 'set the array variable to one



'Disable uneeded functionality to help macro run faster
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

'loop through the array and process each spreadsheet
For Each doc In documents 'Loop start

'sub method variables
Dim dSet As cDataSet, dsClone As cDataSet, jo As cJobject, cb As
cBrowser
Dim sWire As String


'get the google wire string
Set cb = New cBrowser

'had to add this in, the for loop continued to iterate beyond the
size of the array (didn't do this in the previous cDataSet program)
If i = 4 Then
Exit Sub
Else

'get the string from each array element
sWire = cb.httpGET(documents(i))

'load to a dataset
Set dSet = New cDataSet
With dSet
.populateGoogleWire sWire, rng

If .Where Is Nothing Then
MsgBox ("No data to process")
Else
'it worked
End If
End With
End If

'increment loop counter
i = i + 1
'clean up
Set dSet = Nothing
Set cb = Nothing

'move the cell focus down for the next spreadsheet to
'be printed out- set to the size of the clients worksheets
Set rng = rng.Offset(99, 0)
rng.Select

'Move on to the next Google Doc
Next doc 'Loop end

'//These are additional functions that I've tested in previous
iterations but am not ready to test here yet.

'Function call to remove any duplicated information from
'the spreadsheet prior to importation to the clients
'worksheet.
'mRemove.CheckForDups

'Function call to delete any blank spaces left after duplicate
'information was removed in the previous function.
'mCondense.RemoveBlanks

're-enable functionality
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub

Thanks for your help and I hope your trip is going well.

~Allen


On Aug 15, 9:08 pm, bruce mcpherson <br...@mcpher.com> wrote:

> Hi Allen
>
> Hi Allen
>
> Glad you got it going.. I think you may need to revisit though if this is
> supposed to be interactive - Excel quickly goes down the toilet with a lot
> of data. Let me know how it works out.
>
> I'm in US for the week actually - going back to UK on saturday
>
> bruce
>

> ...
>
> read more »

[AL3537]

Never mind-

I found the boolean values I needed to change in the cDataSet class
that prevented the imports from being erased- works like a charm!

Public Function populateJSON(job As cJobject, rstart As Range,
Optional wClearContents As Boolean = True)
changed to False

Public Function populateGoogleWire(sWire As String, rstart As Range,
Optional wClearContents As Boolean = True)
changed to False

regards,
Allen

> ...
>
> read more »

[bruce]

Allen

Better not to change the classes, since it means that you wont be able
to use new versions without changing them too.

You can do all that you have done below using optional parameters -
you should download the latest cdataset first though - i loaded a new
one today. There's also a filter procedure on there you should look at
btw, that might be of some use to you.

Here's what your procedure should look like below. Just copy down the
latest cDataSet and add the module below

Note also that you don't need to bother with the screenupdating off
etc, since the sheet is committed in one shot (rather than a cell at a
time) there's no real benefit. If you want to , you can read about
the profiling on that here http://ramblings.mcpher.com/Home/excelquirks/classeslink/classes,
and how to do optimization profiling here
http://ramblings.mcpher.com/Home/excelquirks/optimizationlink

cut down version ...
Option Explicit

Public Sub getGoogleDocs()
Dim documents(1 To 3) As String

'Array objects
documents(1) = "https://docs.google.com/spreadsheet/tq?
range=A1:K98&key=0AtpooRRcZnFRdENvUXVXWXVSX3h4c2JSR0s0RWpnRnc&gid=0"
documents(2) = "https://docs.google.com/spreadsheet/tq?
range=A1:K98&key=0AtpooRRcZnFRdENvUXVXWXVSX3h4c2JSR0s0RWpnRnc&gid=1"
documents(3) = "https://docs.google.com/spreadsheet/tq?
range=A1:K98&key=0AtpooRRcZnFRdENvUXVXWXVSX3h4c2JSR0s0RWpnRnc&gid=2"

Dim rng As Range, i As Long

'sub method variables
Dim dSet As cDataSet, dsClone As cDataSet, jo As cJobject, cb As
cBrowser
Dim sWire As String

Set rng = Range("a1")

'loop through the array and process each spreadsheet

For i = LBound(documents) To UBound(documents)

Set cb = New cBrowser

'get the string from each array element

sWire = cb.httpGET(documents(1))

'load to a dataset
Set dSet = New cDataSet
With dSet

.populateGoogleWire sWire, rng, False, i =
LBound(documents)

If .Where Is Nothing Then
MsgBox ("No data to process")
Else

Set rng = rng.Offset(.RowCount).Resize(1, 1)
End If
End With
Next i

'clean up
Set dSet = Nothing
Set cb = Nothing

End Sub

> ...
>
> read more »

[AL3537]

Hi Bruce,

Thanks for the advice- need your expertise for another common (or so I
might think) problem.

I have a worksheet(we'll call it A) with, say 8 columns (and of course
a header for each). every cell within a column row (for say 100 rows
south) is linked to another sheet that's bringing in external data
(call it B). In a perfect world, all the data is great and everything
transfers over, however, if (B) has fields that are null, sheet A will
contain blank row(s) in between rows of real data. I created a
function that looks for null data in sheet (B) and promptly disposes
of it. Unfortunately, while deleting rows in sheet B, it also wreaks
havoc on the links to it within sheet A. Is there a method that can be
used to condense the usable data upwards (without deleting rows and
killing links or formulas)?

On Aug 18, 7:34 pm, bruce <br...@mcpher.com> wrote:
> Allen
>
> Better not to change the classes, since it means that you wont be able
> to use new versions without changing them too.
>
> You can do all that you have done below using optional parameters -
> you should download the latest cdataset first though - i loaded a new
> one today. There's also a filter procedure on there you should look at
> btw, that might be of some use to you.
>
> Here's what your procedure should look like below. Just copy down the
> latest cDataSet and add the module below
>
>  Note also that you don't need to bother with the screenupdating off
> etc, since the sheet is committed in one shot (rather than a cell at a
> time) there's no real benefit.  If you want to , you can read about

> the profiling on that herehttp://ramblings.mcpher.com/Home/excelquirks/classeslink/classes,
> and how to do optimization profiling herehttp://ramblings.mcpher.com/Home/excelquirks/optimizationlink

> ...
>
> read more »

[AL3537]

Just Thinking~

I'm a few years removed from using Excel on a regular basis, but for
what I'm explaining (and asking), there is no direct method to
achieving this- however, correct me if I'm wrong, but an indirect
VLookup will probably be the most effective way of accomplishing this.

A- target spreadsheet
B- holding pen
C- external data collection

C- data is collected there; B has the direct links for collecting the
data (can be on the same sheet) without having to dispose of nulls. A
will use a VLookup to collect relevant data from B and populate it's
rows accordingly. Am I on the right track here?

> ...
>
> read more »

[bruce]

Allen

Probably better to copy over then delete in the target sheet -

Set r = Range("sheet1!b2")
If IsEmpty(r) Then
r.EntireRow.Delete Shift:=xlUp
End If

Is this what you need? It will delete a row while preserving the
relative formulas.

btw - might be better to start a new topic- this one is getting long
and this is new subject

Bruce

> ...
>
> read more »

[bruce]

Yes .. vlookup is a good method of doing this. you'll still have blank
lines though

bruce

> ...
>
> read more »

[Alejandro Quiceno Garcia]

Bruce, google docs changed to drive, and the range you mention in your article

Get Data From Google Docs http://ramblings.mcpher.com/Home/excelquirks/get-data-from-google-docs

as: "https://spreadsheets.google.com/a/mcpher.com/spreadsheet/tq?range=A1:H14&key = 0At2ExLh4POiZdHBJYnlwaERpYTRZSWl1VEw1TEpQVkE & gid = 0"

I can not find the option where to see it. I'm trying to implement the exercise, I see that your example e DataSet.xlsm still works well, but I try to do it on my own google spreadsheet to Excel.

I'll be very grateful if you help me implement this exercise.

By the way thank you for this great encouragement to teach these things.

My email is : alejoq...@gmail.com

Alejandro Q.

[Bruce McPherson]

Can you provide a link to the spreadsheet you are trying to import...

Sent from my iPad

--
You received this message because you are subscribed to the Google Groups "Excel Liberation" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-ramblin...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Alejandro Quiceno Garcia]

Thanks for the quick reply!

this is my google spreadsheet example:

https://docs.google.com/spreadsheet/ccc?key=0AodxbO8eOvBZdE93VnNiaVNRdjdxMXJNMWJlNVRMWGc#gid=0

Thks a lot

[Bruce Mcpherson]

Alejandro,

. Go the to sheet named 'Clone' in cdataset.xslm

. open the VBA editor, and go to the module called googlewireexamples

. you'll see a const there which has the link for the sheet to download

change it to your sheet as below

Const url = "https://spreadsheets.google.com/a/mcpher.com/spreadsheet/tq?key=0AodxbO8eOvBZdE93VnNiaVNRdjdxMXJNMWJlNVRMWGc#gid=0"

run the sub googlewireexample, and it will import your google sheet into the excel Clone Worksheet

bruce

[prasun....@gmail.com]

Hello,

I am trying to use the example to extract data from my google spreadsheet into excel.

The link for my spreadsheet is :

https://docs.google.com/a/tmns.com/spreadsheet/ccc?key=0AiBVQ8KXJLPedGVaMzIyZUg2UWlMMGREZktINGltREE#gid=0

However, when I run the googlewire example with the above link, it gives me a message saying "did not find table definition data" and no data is imported. Am I missing anything here?

[Dave Mac]

Hi,

I ran into the same problem. Am in the middle of trying to figure it out hence my viewing of this forum.

So far I found that:

• Sheet must be shared (i use 'anyone with link')
• change the URL from https://docs.google.com to https://spreadsheets.google.com
  
• also change ccc? to qt?

like this...

https://spreadsheets.google.com/a/tmns.com/spreadsheet/qt?key=0AiBVQ8KXJLPedGVaMzIyZUg2UWlMMGREZktINGltREE#gid=0

Im basically hacking my way to a solution, so I dont really understand the above.

The code runs, however I am getting errors such as the below.

Expecting [ got --({[)--  Bad JSON at character 1848 ......

I wonder if google has changes something again... am still working my way through the code to try and understand the parser.

Regards,

David

[Bruce Mcpherson]

 I get this when i try to access your link, so it may still be permission based problem.. btw there is an asynchronous loader now

http://ramblings.mcpher.com/Home/excelquirks/classeslink/vbapromises/complexwebservices

You need permission

Want in? Ask the owner for access, or switch to an account with permission. Learn more

On 25 September 2013 12:23, Dave Mac <che...@gmail.com> wrote:

Hi,

I ran into the same problem. Am in the middle of trying to figure it out hence my viewing of this forum.

So far I found that:

• Sheet must be shared (i use 'anyone with link')
• change the URL from https://docs.google.com to https://spreadsheets.google.com
  

• also change ccc? to tq?

like this...

https://spreadsheets.google.com/a/tmns.com/spreadsheet/qt?key=0AiBVQ8KXJLPedGVaMzIyZUg2UWlMMGREZktINGltREE#gid=0

Im basically hacking my way to a solution, so I dont really understand the above.

The code runs, however I am getting errors such as the below.

Expecting [ got --({[)--  Bad JSON at character 1848 ......

I wonder if google has changes something again... am still working my way through the code to try and understand the parser.

Regards,

David

On Monday, August 19, 2013 2:02:30 PM UTC+1, prasun....@gmail.com wrote:

--

[Dave Mac]

hi bruce,

There seems to be slight format differences when looking at the JSON response in my spreadsheet. am not sure if ive configured things correctly, however....

e.g. your original examples of xlLiberation,

rows:[{c:[,{v:new  Date(2007,5,20),f:'6/20/2007'},{v:'Cellphone'},{v:'cell'},{v:'smart'},{v:''},{v:200.0,f:'200'},{v:''},{v:''}]},

my data ...

"rows":[{"c":[{"v":new Date(2013,7,23,14,43,3),"f":"23/08/2013 14:43:03","p":{"style":"background-color: rgb(238, 238, 238);"}},

My original objective was to try and automatically get data out of a google spreadsheet and into excel to create a dashboard for live form data capture (the Google graphs implementation on a separate Google spreadsheet tab was falling over)

My research lead me to xlLiberation and your cDataset class.  Additional research had lead me to another solution which i'll probably pursue for time being as it gives me the immediate results I need.

The Google Wire protocol allows you to request the response in a number of formats.

https://developers.google.com/chart/interactive/docs/dev/implementing_data_source?csw=1#responseformat

Having chosen CSV e.g.

https://spreadsheets.google.com/spreadsheet/tq?tqx=out:csv&key=#########

I can feed this directly into a query table:-

ThisWorkbook.Sheets("LQTData").QueryTables.add(Connection:="TEXT;" & url, Destination:=Range("$A$1"))

Now I'll hook an ontime event to Querytable Refresh every 2 minutes and Ive got myself a dashboard.

Regards,

David

[Bruce Mcpherson]

ok .. the difference in format btw, was a change in protocol google introduced to make it valid json. Cdataset can handle both. 

In the asynchronous example I sent you, I use google viz to get the data instead of interpreting the proto directly, so its anyway less susceptible to format changes. 

bruce

[dave.mc...@gmail.com]

Thanks Bruce,

I'll give that a go.  

Am remain curious as to why mine doesnt work. I simply changed the url constant in the google wire example module. (I'll post the url if you think its something I've not set up correctly.)

I note that the google vis api has the ability to adopt views, group etc.  Do you have plans to explore that in your class?

Also, is there a way to post data into spreadsheets with your classes?

Regards,

David