How to write a macro in excel 2003 code#
Replace xxxxxxxx in your code with your form's respective numbers.You will need these numbers for each field. Look for the entry.123456789 for each of the fields. Note a “?” is required at the end of the url.Īlso under headers find “Form Data”. Under “General” copy the url of the form.Click on “Headers” Click on “formResponse”.Enter some data (anything) for each of the respective fields and click on “Submit”.Write-click and “Inspect” (Ctrl-Shift-I) and click on Network.Now click on “Form” – “Go to Live Form”.Go back to the Google spreadsheet and it should have created a new sheet (called "Form Responses") with headings matching the field names given when setting up the form.Ĭolumn A will automatically have as a heading “Timestamp” with your field names as headings for columns B, C & D etc. Create a form (under “Tools” – “Create a form”) with a three fields (you can add fields later after testing).None seemed to work for me so I had to utilize bits and pieces from various posts I found on-line. I spent the last couple of days trying to find a good easy solution to this problem.
How to write a macro in excel 2003 how to#
This is an ASP example of how to use that OAuth library again since both the ASP and the VBA are using the VBScript syntax, it could probably be adapted.
You should be able to adapt this OAuth 2.0 ASP library for your VBA code. In your VBA, you essentially use the following to send XML requests: Dim x as MSXML2.XMLHTTP You can play with the OAuth requests here, which should help you get started.ĪPI functions are called by GET/POST requests with XML, which you can call using the XMLHTTP object.įirst, reference Microsoft XML in your Excel project (Tools->References->Microsoft XML, v6.0) The OAuth 2.0 link that the spreadsheet docs refer to is out-of-date. In that light, here is my original answer, if it helps. The ASP OAuth below is probably usable with some work, but I noticed it uses Session variables and some other ASP objects, so you'd have to do a lot of tweaking. The reason this is non-trivial is the authentication part. I think it would be a lot easier if you could make your Google spreadsheet public, but I doubt that is advisable with sales data. I had started answering your question, but realized that it was much less trivial than I thought it was when I started playing with the OAuth 2.0 API. HttpRequest.Send "entry.0.single=" + column1 + "&entry.1.single=" + column2 + "&pageNumber=0&backupCache&submit=Submit" tRequestHeader "Content-Type", "application/x-www-form-urlencoded" Replace column1 with your desired values. Add a reference to "Microsoft XML, v3.0" before. Next we try to execute the form POST from our Excel sheet via the following code. 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 "" -d "entry.0.single=test&entry.1.single=test2&pageNumber=0&backupCache=&submit=Submit"
Create a spreadsheet or open an existing one.You just need to prepare your spreadsheet to accept data entries via a form as follows: Google Spreadsheet allows data entry with a simple form, which means also that a HTTP POST will do the trick.
You don't need OAuth or the spreadsheet API.
0 kommentar(er)
