Query An Xml Document With Ado

Mar 30, 2007

I was able to create a recordset of data and then save the recordset as an XML document. This XML document is aggregated data for a chart in Excel.

I need to hit the XML with ADO into a recordset from my select statement...

As you can see below, I think I found the Driver I need to use??? I am not sure that this is correct for Excel VBA ADO???

sSQL = "Select * From C:ADO.XML"

When I open the recordset.open sSQL, cnDB the error I get is this:
"The filename, directory name, or volumne label syntax is incorrect"


Set GetXMLDB = New ADODB.Connection

With GetXMLDB
.Open "Provider=MSDAOSP; Data Source=MSXML2.DSOControl.2.6;"
.CursorLocation = adUseClient
End With

View 9 Replies


ADVERTISEMENT

Web Query Online Pdf Document

Mar 12, 2008

Is it possible to extract data via a webquery from an online PDF document? Like this one: http://www.paalp.com/_filelib/FileCa...r2007Recap.pdf

View 2 Replies View Related

VBA To Query SharePoint Document / Folder Permissions

Jan 4, 2012

I'm using VBA to query file information (path, name, size, dates, etc.) on files on a SharePoint site and returns them to Excel. I'm curious if also returning the document's permissions is possible. If not for the document, can one programmatically get the folder's permissions?

View 6 Replies View Related

Searching For Mistakes In Document While Using Another Document As Reference

Feb 2, 2014

I am processing a fair few Invoices, which are being sent to me via e-mail as excel documents, very often they contain mistakes, a decent amount of mistakes. Usually the prices are wrong.

I keep track of every single entry on the invoice on my own document - Tracker, which I consider to be the superior/more correct document to the Invoice presented to me by my contractor.

Both of the documents have a reference number, which is a specific docket number, and horizontally, in the invoice, there is going to be a price for this docket. In my document, there is going to be a separate column for the total price.

Is it possible (I guess with VBA) to check for mistakes in the Invoice, but use the Tracker as a reference for this check.

Tracker has
columns
A - Name
B - Department
C - Date
D - Docket No.
C - Total price for the docket (calculation of E to Z)
E to Z - all smaller entries

Invoice has
columns
A - Date
B - Docket No.
C to E price for that docket, but it is spread, because departments are separated out, so each VAT account can be charged accordingly. I guess it is possible to do a separate column for the price, if it is easier to do a script that way.

Basically, I need to check if in the Invoice document, the price (C to E) for Docket No. (B) is the same as the price (C) for the Docket No. (D) in the Tracker.

I would like the wrong entries to be highlighted on the Invoice Document, so I can see straight away, that this needs attention.

Not always the price is wrong, sometimes the Docket No. is spelled incorrectly (Dyslexic contractor), hence the highlighting.

View 3 Replies View Related

Script That Will Turn Excel Document Into A Txt Document

Feb 17, 2009

I need a script that will turn a excel doc into a txt doc. Thats the easy part. The hard part (at least I think it is), is I need it to be in a certain format and I'll do my best to explain that fomat below.

View 9 Replies View Related

Pass Parameter From Excel Through MS Query To MS Access Query

Nov 26, 2012

I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.

I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.

Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.

If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?

Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.

View 3 Replies View Related

Query Parameters Which Takes The Date From The Cell Into The Query

Mar 29, 2007

Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.

My query is this
WEB
1
http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12

Selection=15
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

View 2 Replies View Related

Create An Conection With MS QUERY To A Csv And Query Data

Sep 6, 2008

I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run.
I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!

View 9 Replies View Related

Query An Access Query With VBA Function

Jul 8, 2008

I'm trying to query a query in Access 2003, from Excel 2003.

The query in Access looks like:
AccessQuery: [SELECT VBAFunction(field1) FROM Table]

The query in Excel looks like:
ExcelQuery: [SELECT * FROM AccessQuery]

I use the following VBA code in Excel to excecute the query:

With ThisWorkbook.Worksheets(cDataSheetName).QueryTables.Add(Connection:=strConnection, _
Destination:=ThisWorkbook.Worksheets(cDataSheetName).Range("A1"), Sql:=strQuery)
.RowNumbers = True
.Refresh BackgroundQuery:=False
iResultRowCount = .ResultRange.Rows.Count
End With
When I execute this code I get the error message 'SQL Syntax Error' (Error 1004). When I remove the VBA function from the query in Access, it all works fine.

View 9 Replies View Related

Edit Query Using Query Wizard

Apr 25, 2006

I created an Ms Excel Database Query to bring in data from MS Access. (versions 2002 of MS Excel and Ms Access). The query works fine initially. I can right click, choose Edit Query and change my criteria. Results are returned almost instantly.

My problem is that, once I save the workbook, or autosave happens, I get an error when I right-click to Edit Query: This query cannot be edited by the Query Wizard..

View 2 Replies View Related

MS Query - Parameters (parameters On A Query But Use A Wildcard To Return All Instances)

Nov 17, 2009

Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.

View 4 Replies View Related

Re-arrange The Document?

Aug 4, 2008

I have an excel document with a list of about 12,000 names/addresses. They are currently displayed like this:

A B C
1 <name1> <name2> <name3>
2 <address1> <address2> <address3>
3 <zip1> <zip2> <zip3>


...and I want to re-arrange them so they are displayed horizontally:

A B C
1 <name1> <address1> <zip1>
2 <name2> <address2> <zip2>
3 <name3> <address3> <zip3>

Is there any possible way of doing this quickly without copying/pasting 12,000 lines of text?

View 2 Replies View Related

Cannot Sort Whole Document

Oct 31, 2013

I am trying to sort a document alphabetically but cannot. I have two lines in the doc that previously had headers on them. I was able to remove them, but am now left with a row that has what looks like a backwards L in the last cell. The bottom line is also darker, but there is not a detectable border set. If I choose the rows that include these rows in my sort option, it is disabled. Sorting is available if I do not. I need to sort everything.

View 3 Replies View Related

Generate New Document

Nov 12, 2008

I have an excel document that is a form.

Can I use the data collected from this form to generate another new document?

I want the user to enter some data and hit "Create" and a new excel document with that data is created.

Also, the new document has some data in it already. So it has some data and it will get new data from the form.

View 3 Replies View Related

Can I Autosave A Document?

Sep 11, 2009

Is there a way that I can have an excel document "autosave"?

Specificaly I would like: If a value in a specific cell's value is changed I would like the document to automatically save the document as the name of the value that is typed into that cell when the user closes the document.

I'm not sure if this is possible, but it would be great if there was some way to do this.

View 14 Replies View Related

Protecting The Document

Jan 15, 2008

Version used: Excel 2003

I have a spreadsheet that everyone in my department uses. My boss has asked me to protect certain columns so that only I can edit them but leave others so that the rest of the depratment can use those.

I have tried locking the ones I am to use and unlocking the ones the others can use then protecting the document. This works well enough as far as it goes, however, it is combersome having to unprotect then reprotect it every time I need to work on it. Also, that method makes the drop down lists that we are using for easy sorting no longer function.

View 9 Replies View Related

Document Stability

Mar 18, 2009

I am currently building a program in Excel, which can carry out numerious tasks. Having over 15 userforms, countless macros and several worksheets of caculations in the background, basically all geared towards capturing customer data, producing a application form and submitting the data to an ".mdb" file.

Now everything is going well so far with little in the way of problems and the document is 2.5Mb in size (users centrally access this Read Only). I'm conducting a daily Clean Project on this but I have a few questions...

1) how far can I push an Excel workbook in terms of size/content before it becomes unstable

2) are there any tips that can be offered for program stability

3) Has anyone built anything on this scale before

...the reason I ask is the program could potentially double in size over the next 6 months depending on the volume of application types it is able to produce. I want to make sure I have considered stability.

View 9 Replies View Related

Macro For Document

Feb 8, 2010

i need macro for my document.this macro need run on column "E" & "H" i need like this.

ex- E2 cell some text or number is there,H2 cell value should delete.E2 cell is empty H2 value no need to delete.

View 9 Replies View Related

Word Document Of Comments

Feb 22, 2008

I have an excel worksheet which has a lot of comments attached to individual cells. Is there a way to convert these comments into a microsft word document along with the contents of the cells to identify where they originated? Also Is it possible to convert just a selection of cells into word document rather than the whole worksheet.

View 9 Replies View Related

List Recent Changes To Document?

Oct 22, 2007

Is there a way to list the recent changes to an excel document on the document itself?

For example, I have just 4 or 5 columns in the sheet, but out the the right on the 7th column I'll have a header that says "Recent Changes". Below that for X amount of rows, I would like it to keep a running change list something like this:

B14 was changed from '6.021' to '6.5' by username on 10/21/07 3:45 pm
A23 was changed from 'Sally' to 'Billy' by username on 10/21/07 2:30 pm

I thought this up by looking at the way the "Track Changes" functionality works on the Tools menu. That will highlight the cells in blue as they are changed and will also let someone look back through changes to let them accept or deny.

View 4 Replies View Related

Macro To Format A Document

Dec 20, 2007

I am recording a macro to help me with formatting a document. I know do this manually, but realized that if I record a macro and run it the process would be faster. I have tried this and it works really well. However the problem is that although the columns are always the same, the rows increase and decrease everytime I download the raw report.

When I record the macro and then run it, it only selects the number of rows that I choose when I record the macro. I am sure there is a way to have the macro look for date and then stop after there is no more rows, but I can't find it in help.

I have tried selecting the whole document, but that slows the automated formatting down significantly.

View 9 Replies View Related

Creating A CSV Document From A Sheet

May 15, 2008

I've got an Excel document that is generated from a third party programme (See ExcelDoc attached)

I'm trying to manipulate it so that a macro creates a CSV file like the one attached (see THK.csv)

I need to have the following columns in the CSV file populated using the Excel spreadsheet
Column A: Will need to have the NUMBER that is in cell B2 of the Excel file in this case the number is 0714 (need to keep the preceeding 0 too)
Column B: Will need the phone number
Column C: Will need the fax number
Column D: Will need to have the email address
Column E: Will need to have the date only
Column F: Will need to have the Order Number
Column G: Will need to have the Customer name
Column H: Will need to have the Customer telephone number
Column I: Will need to have the Customer alternative telephone number
**Column J: Will need to have the 1st line of the address (after First Address in xls doc)
Column K: Will need to have the 2nd line of the address
Column L: Will need to have the 3rd line of the address
Column M: Will need to have the 4th line of the address
Column N: Will need to be a blank cell
Column O: Will need to be a blank cell
Column P: Will need to be a blank cell
Column Q: Will need to be a blank cell
Column R: Will need to be a blank cell
Column S: Will need to be populated with the information that is after the field MyAddress: in Cell B11 of the xls document.

I've managed to get a macro that will take the information and transpose it into a new document and rename it as an CSV file but I am having a lot of problems trying to get the macro to create the CSV file in the format that I need.

**This cell is generated and will have commas at the end of each line of the address and will be terminated by a period (at line 4 of the address. Sometimes there will be 4 lines to an address but sometimes there may only be 2 or three. In the case where there is no information I need to have a blank (null) value in the cell

View 9 Replies View Related

Open Word Document From VBA

Jan 19, 2009

I'm trying to open a word document from excel using VBA. Here is my code.

View 5 Replies View Related

Manipulate Notepad Document

Jan 20, 2009

I'm using the following code to copy columns of data in a worksheet of mine. The code once activated will open "notepad" and copy the columns of data in my excel worksheet. Here is the

View 6 Replies View Related

Macro For Printing A Document

Aug 9, 2009

I need a macro for printing a document, Could anyone supply me with the macro. And how to acivate it and sve in the sheet

View 5 Replies View Related

Opening Word Document From VBA

Aug 25, 2009

I am trying to open a word document from within excel using

View 2 Replies View Related

Export Worksheet Into PDF Document

May 10, 2012

I am trying to get some code that will export an worksheet called 'Data' into a PDF document.

View 4 Replies View Related

Save Word Document As PDF?

Sep 28, 2013

Amend this code? I want to save the word document, then save it as a pdf file, then delete the word document;

Code:
Sub E_W()
Dim strDate As String
Dim DirName As String

[Code]....

View 3 Replies View Related

Kicking User Out Of Document

Aug 7, 2008

I've created a folder and spreadsheet on a shared drive. Since I am the Author of the document, Can I kick a user out of the document if I wish.

View 9 Replies View Related

Covert A Word Document

Jan 25, 2009

I need to covert a word document to excel.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved