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

问题描述

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

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

我需要点击按钮"，它会将该行添加为 Google 电子表格

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

类似于:

有可能吗?

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

Should I use the command-line Google tools?

有没有更好的办法?更简单的方法?

Is there a better way? Easier way?

你会怎么做?

(一旦我知道如何从 VBA 添加东西"到 Google 文档，我该如何将它添加到最后一行?)

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

更多信息:我有一个 Excel 2003程序"，可以保存公司的所有销售额(包括客户信息)，并且我想做一个全球通讯录，我的(非它)同事可以轻松更新.

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.

推荐答案

您不需要 OAuth 或电子表格 API.Google 电子表格允许使用简单的表单输入数据，这也意味着 HTTP POST 可以解决问题.您只需要准备电子表格以通过以下表单接受数据输入:

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:

• 登录您的 Google 文档帐户
• 创建电子表格或打开现有的电子表格
• 点击工具/创建表单
• 在表单描述中添加任何内容以启用保存"按钮
• 保存表单
• 复制表单创建页面底部链接中显示的formkey值
• 现在您可以在没有 OAuth 的情况下在电子表格中发布一个简单的帖子

如果您的系统上有 curl，您现在可以测试该条目(将 formkey 占位符替换为表格中的表单键):

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"

接下来，我们尝试通过以下代码从我们的 Excel 工作表中执行表单 POST.之前添加对Microsoft XML，v3.0"的引用.将 column1 替换为您想要的值.

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

希望有帮助

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