ACCESS FEED: Re: [MS_AccessPros] Re: Talking to Excel quit working

Hi Walter,

Oh happy day! I am glad you got it -- sometimes that happens. I cannot tell you how many perfectly good lines of code I have deleted just to type them again because something wasn't working right.

My code documenter took all weekend to finish so I am behind -- got a project I need to use the results of my documenter on pronto! ... so it will be a bit of time before I can look at your files, thanks for sharing :)
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:
I GOT IT TO WORK AGAIN!!!
I seems it was an Excel issue, I removed all traces of the xlsx file and initiated a new spreadsheet by dblclicking on the iqy file and renamed it the old file name, told it where to find the search parameter and darn if it didn't come back from the dead!
I uploaded the new and improved file (same zip file name ,same place)
tell me what you think.
Walter

--- Crystal <strive4peace> wrote:
>
> thanks, Walter, I got it -- look forward to having time for it. Can't do anything today, still scrambling for rent due in a couple days.
>
>
> 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:
> The problem app is in the assistance needed folder "Library_V1_Excel.zip"
> Walter
>
>
> --- "nkpberk" <wgriffin48> wrote:
> >
> > Crystal;
> > I will have to resurrect a copy of the Excel version of this project.
> > Have been playing with the "ImportXML" version with problems of inconsistent data formatting from the LoC. The Excel version seems the only way to import the Marc schema completely.
> > I'll see how soon I can put the Excel version together.
> > Walter
> >
> >
> > --- Crystal <strive4peace> wrote:
> > >
> > > Hi Walter,
> > >
> > > can you upload a sample with all the files so I can test it ? Since it used to work, I might see some issues that hang later versions as I have done a lot of conversions. Older versions of Access are more forgiving about sloppy coding. In fact, in the beginning of last summer Access was more forgiving, it seems to me, no matter what version was being used. I have had to make some odd changes to get things to work lately ...
> > >
> > >
> > > What is prompting me to write the Code Documenter I am developing right now is a huge Access 97 conversion to 2010 ... keep thinking "today I will be done". There is so much more the compiler can check so I am documenting all the code. Then I am going to write code to change the code ... will use results from my Analyzer to construct Find and Replace phrases for all the field references. Well that is beside the point. I can't wait till I am done. I already made a list of all the databases I am going to analyze the code in ;)
> > >
> > > Back to the point: I would like to see how your app works so I have a personal interest in helping you.
> > >
> > > Warm Regards,
> > > Crystal
> > >
> > > *
> > >    (: have an awesome day :)
> > > *
> > >
> > >
> > >
> > > ________________________________
> > > From: nkpberk
> > >
> > > Crystal;
> > >
> > > One of the points I am trying to stress is this used to work without fail! I got the example code from John 8 or 10 months ago. What I cannot get to work is to send the parameter string to Excel from Access and have it appended to an ".iqy" file to go to the web page.
> > > I have gone so far as to remove all occurrences of the LocImport.xlsx from the drive and start over.
> > > I know nothing about XML, if I had an "xsd" file I wouldn't know what to do with it. I am counting on Excel to (for lack of a better word I'll make one up, Kinda violates the I before E thing tho ;-) "Tableize" the recovered data. After that I can pick out the information I need to create the inventory record.
> > > My antivirus program has office files scan disabled, all the files for this app are in the same sub directory and trusted locations are set in both Excel and Access. I don't know what else I can do.
> > > Walter
> > >
> > >
> > >
> > > --- Crystal <strive4peace> wrote:
> > > >
> > > > 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]