如何写入“Google电子表格”来自Excel 2003 VBA [英] How to write to a "Google Spreadsheet" from Excel 2003 VBA

问题描述

我有一个 Excel 2003 文件，其中有一行类似于此：

我需要点击按钮和它将该行添加为 Google Spreadsheet

中的最后一行

类似于：

可能吗？

我应该使用命令行Google工具吗？

有没有更好的方法？更简单的方法？

你会怎么做？

（一旦我知道如何添加东西 VBA 转换为 Google文档，我如何将它添加到最后一行？）

我有一个 Excel 2003 程序，可以节省公司所有的销售额（包括客户信息）一个全球地址簿，很容易由我的（非IT）同事更新。 解决方案

您不需要OAuth或电子表格API。 Google电子表格允许用简单的表单输入数据，这意味着HTTP POST也可以做到这一点。您只需准备电子表格即可通过表单接受数据输入：

• 登录您的Google文档帐户


• 创建电子表格或打开现有电子表格
  
• 单击工具/创建表单


• 在表单描述中添加任何内容启用保存按钮


• 保存表单


• 复制表单创建页面底部链接中显示的formkey值


• 现在您可以在不使用OAuth的情况下向电子表格发布简单的帖子

您现在可以使用<如果你的系统上有它（用表格中的formkey替换formkey占位符）：

  curl.exe -v -khttp://spreadsheets.google.com/formResponse?formkey=<formkey> -dentry.0.single = test& entry.1.single = test2& pageNumber = 0& backupCache =& submit = Submit
  

接下来，我们尝试通过以下代码从我们的Excel工作表中执行表单POST。之前添加对Microsoft XML，v3.0的引用。

  Dim httpRequest as XMLHTTP 
设置httpRequest =新建XMLHTTP 
 httpRequest。打开POST，http://spreadsheets.google.com/formResponse?formkey=<formkey>&amp;ifq，False 
 httpRequest.setRequestHeaderContent-Type，application / x- www-form-urlencoded
 httpRequest.Sendentry.0.single =+ column1 +& entry.1.single =+ column2 +& pageNumber = 0& BackupCache& submit = Submit 
 
'检查下列变量的结果
 httpRequest.status 
 httpRequest.statusText 
  

希望有帮助

I Have an Excel 2003 file with a line similar to this:

I need to click "the button" and it adds that line as the last one on a Google Spreadsheet

Similar to:

Is it possible?

Should I use the command-line Google tools?

Is there a better way? Easier way?

How would you do it?

(once I know how to add "stuff" from VBA to Google Docs, how the f do i add it to the last line?)

More info: I have an Excel 2003 "program" that saves all of the company's sales (with the customer info), and I'd like do make a global address book that's easily updated by my (non it) co-workers.

解决方案

You don't need OAuth or the spreadsheet API. Google Spreadsheet allows data entry with a simple form, which means also that a HTTP POST will do the trick. You just need to prepare your spreadsheet to accept data entries via a form as follows:

• Login to your Google Docs account
• Create a spreadsheet or open an existing one
• Click on Tools / Create a form
• Add anything in the form description just to enable the Save button
• Save the form
• Copy the formkey value displayed in the link at the bottom of the form creation page
• Now you can issue a simple post into the spreadsheet without OAuth

You can test the entry now with curl if you have it on your system (replace the formkey placeholder with the formkey from your table):

curl.exe -v -k "http://spreadsheets.google.com/formResponse?formkey=<formkey>" -d "entry.0.single=test&entry.1.single=test2&pageNumber=0&backupCache=&submit=Submit"

Next we try to execute the form POST from our Excel sheet via the following code. Add a reference to "Microsoft XML, v3.0" before. Replace column1 with your desired values.

Dim httpRequest as XMLHTTP
Set httpRequest = New XMLHTTP
httpRequest.Open "POST", "http://spreadsheets.google.com/formResponse?formkey=<formkey>&amp;ifq", False
httpRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
httpRequest.Send "entry.0.single=" + column1 + "&entry.1.single=" + column2 + "&pageNumber=0&backupCache&submit=Submit"

'Check result in the following vars
httpRequest.status
httpRequest.statusText

Hope that helps

这篇关于如何写入“Google电子表格”来自Excel 2003 VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！