Hi Walter,
You xml file is multi-level. It is my opinion that Excel has a better tool for importing xml than Access. Excel can point to the XML Schema file which has XSD extension. The schema file is necessary to interpret more than one level of data
If you still have questions, would you please post your database, specify what to pick and click, and what you expect the results to be? Please include xml file, xcd file, and any other source files in the ZIP file that you post, thank you
> "understand why the "xlapp.quit" won't stop the instance of Excel when it executes in code?"
probably because you have another Excel object variable (such as workbook or worksheet or range) you have not released
~~~
please do not delete posts on the thread -- they are necessary to help trigger things, thank you
Warm Regards,
Crystal
Microsoft MVP
remote programming and training
Access Basics by Crystal
http://www.AccessMVP.com/strive4peace
Free 100-page book that covers essentials in Access
*
(: have an awesome day :)
*
________________________________
From: nkpberk
Hi Crystal;
I tried all of your suggestions and only one worked, using an existing instance of Excel, kept from clogging memory but I could still not get it to close when finished or pass the parameter to Excel
So, I have altered my approach to retrieving the publication data, I've given up on MS Excel to transfer data to Access and gone to MS Access "ImportXML" method within VBA
The "strParameter" is a user entered ISBN or LCCN
{Build URL String}
strSearch =
"http://z3950.loc.gov:7090/voyager?operation=searchRetrieve&version=1.1&recordSchema=marcxml&startRecord=1&maximumRecords=1&query=bath.standardIdentifier=" & strParameter
{remove tables from last search}
If TableExists("searchretrieveresponse") Then db.TableDefs.Delete
"searchretrieveresponse"
If TableExists("record") Then db.TableDefs.Delete "record"
If TableExists("datafield") Then db.TableDefs.Delete "datafield"
{Retrieve Data}
Application.ImportXML strSearch, acStructureAndData
However, when the import completes I only get one data column (called "subfield") in datafield table, with only some of the data I need to populate the record and it is not consistent in format (I suspect due to differing amounts of information entered at The LoC). When I 'manually'(not programmatically) pulled it in to an excel spreadsheet I got about 14 columns with all the data pertaining to the publication.
Is there a way to alter the URL search string to get all the data?
or some other solution?
Walter
--- Crystal <strive4peace> wrote:
>
> Hi Walter,
>
> you're welcome, have fun studying ;)
>
> I neglected to DIMension xlApp in the sample code I gave you
>
>
> DIM xlApp as Excel.Application 'for early binding
> 'DIM xlApp as Excel.Application 'for latebinding
>
> you didn't compile the code before testing, did you?
>
> '~~~~~~~~~ Compile ~~~~~~~~~
>
> Whenever you change code, references, or switch versions, you should always compile and save before executing.
>
> from the menu in a VBE (module) window: Debug, Compile
>
> fix any errors on the yellow highlighted lines
>
> keep compiling until nothing happens (this is good!) -- then Save
>
>
> ~~~~~ also be sure to use Option Explicit at the top of each module so variables that are not declared or are misspelled will be picked up
>
> Option Explicit ' require variable declaration
>
>
> Warm Regards,
> Crystal
>
> Microsoft MVP
> remote programming and training
>
> Access Basics by Crystal
> http://www.AccessMVP.com/strive4peace
> Free 100-page book that covers essentials in Access
>
> *
> (: have an awesome day :)
> *
>
>
>
> ________________________________
> From: nkpberk
>
> Crystal;
> Thanks, I printed that all out and shall play with it. I just don't understand why the "xlapp.quit" won't stop the instance of Excel when it executes in code?
> Walter
>
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar