VideoHelp Forum
+ Reply to Thread
Page 1 of 2
1 2 LastLast
Results 1 to 30 of 37
Thread
  1. Member
    Join Date
    Jan 2006
    Location
    United States
    Search Comp PM
    I downloaded an Excel template that's an xls file from my state department of revenue which has a button to click to create an xml file when all info in the template is completed. With Excel97 installed it gives message "Compile error: Sub or function not defined" which I'm guessing means Excel97 doesn't support such xml file creation or there's some setting in Excel97 that needs to be changed.

    I was able to create the xml file from the xls file by borrowing a friend's laptop that has Excell2003, but I did have to change the security setting in Excel2003 for it to work. Is there a setting in Excel97 that will create a xml file from a xls file, or is this function not supported in Excel97? If Excel97 doesn't support the function, is there a free Excel alternative similar to Open Office that's similar to Microsoft Word for word processing that will convert xls file to xml file?
    Quote Quote  
  2. Member AlanHK's Avatar
    Join Date
    Apr 2006
    Location
    Hong Kong
    Search Comp PM
    Originally Posted by bevills1
    is there a free Excel alternative similar to Open Office that's similar to Microsoft Word for word processing that will convert xls file to xml file?
    I'm pretty sure OpenOffice "Calc" will.
    Quote Quote  
  3. Member blinky88's Avatar
    Join Date
    Mar 2004
    Location
    Australia
    Search Comp PM
    Your problem is, you are trying to access an Excel file made in a later version of Excel ..eg.. 2003, 2007.
    Quote Quote  
  4. Member AlanHK's Avatar
    Join Date
    Apr 2006
    Location
    Hong Kong
    Search Comp PM
    Originally Posted by blinky88
    Your problem is, you are trying to access an Excel file made in a later version of Excel ..eg.. 2003, 2007.
    Regardless, Excel 97 doesn't know anything about XML - neither does Excel 2000, which I use, for that matter.
    Quote Quote  
  5. Member
    Join Date
    Nov 2004
    Location
    Las Vegas
    Search Comp PM
    bevills1,

    It sounds like there's a problem in a macro. Can you post the macro line where execution stops when you get the error message?
    Quote Quote  
  6. Member
    Join Date
    Jan 2006
    Location
    United States
    Search Comp PM
    As stated in original post the error given there is given when you click "Create xml file" at the bottom of the macro after all info that's needed is complete. If there were something wrong with the macro, it should fail in Excel2003 too, but it doesn't fail in Excel2003.

    I think blinky88 and AlanHK are right that Excel97 doesn't recognize XML as I guessed from the error given.
    Quote Quote  
  7. Banned
    Join Date
    Oct 2004
    Location
    Freedonia
    Search Comp PM
    Originally Posted by bevills1
    is there a free Excel alternative similar to Open Office that's similar to Microsoft Word for word processing that will convert xls file to xml file?
    Open Office is not an alternative to Microsoft Word. It's an alternative to Microsoft Office and thus Microsoft Word, Excel and Power Point and not Word alone. As AlanHK pointed out, the Calc program inside of it should be able to do what you need.
    Quote Quote  
  8. Member
    Join Date
    Jan 2006
    Location
    United States
    Search Comp PM
    I knew Open Office included a word processing program similar to Word included in Microsoft Office, but I didn't know if Open Office had something similar to Microsoft Excel which is what I was asking.

    I downloaded, installed and tried Open Office, but it does nothing when the "Create xml file" button in the template is clicked which must mean that template is specifically for Excel and won't work with other programs similar to Excel.
    Quote Quote  
  9. Member
    Join Date
    Oct 2004
    Location
    United States
    Search PM
    Well I'd be pretty surprised if Excel VBA code executed in Open Office Calc
    Quote Quote  
  10. There's an MS Office 2007 Compatibility Pack that allows previous versions to open and save in 2007 format. It works for me with 2003 but I don't know about '97. Excel 2007 files are xml. Of course, whether the content is of any help is a different matter...
    John Miller
    Quote Quote  
  11. Banned
    Join Date
    Oct 2004
    Location
    Freedonia
    Search Comp PM
    OK. I think I finally understand what is going on now.

    Your worthless piece of @#$! state revenue office has made Office 2007 a requirement. You didn't know that, did you? Yep, they did. You see, this "XML" is not true XML, but it's "Office Open XML". This is Microsoft's bogus attempt to use "XML" but it follows their evil "Embrance and extend" policy where they add so much "extra" to it that it no longer resembles the orignal (in this case XML) and you need to pay Microsoft for something that can use it. This is 100% unnecessary as your state revenue office could have just used Word 2003 format, which is well supported.

    Here's a link on Open Office support for the format, which seems to not be present in most builds of Open Office, hence why it didn't work for you.
    http://en.wikipedia.org/wiki/Open_Office#Microsoft_Office_interoperability
    That link suggests that a Microsoft compatibility pack may work for you. This is what JohnnyMalaria very helpfully suggested.
    Quote Quote  
  12. Yes, very bad move, and the IT morons there should be kicked out....If they're picking MS office as their standard, they should at least pick the lowest common version to save the docs in....morons
    tgpo famous MAC commercial, You be the judge?
    Originally Posted by jagabo
    I use the FixEverythingThat'sWrongWithThisVideo() filter. Works perfectly every time.
    Quote Quote  
  13. Member AlanHK's Avatar
    Join Date
    Apr 2006
    Location
    Hong Kong
    Search Comp PM
    Following some of jMan's links, I find:

    Microsoft provides a compatibility pack to read and write Office Open XML files with Office 2000, XP and 2003.[26] The compatibility pack can also be used as a stand-alone converter with Microsoft Office 97.
    The link is:
    Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 File Formats

    I guess that you still won't be able to run the macro, but if you make your spreadsheet in XLS this should be able to convert it to XML.
    Quote Quote  
  14. Member
    Join Date
    Jan 2006
    Location
    United States
    Search Comp PM
    No, jman98, Excel2003 works as stated in my original post, but Excel97 doesn't work making it necessary to buy either Office2003 or Office 2007. Also the link for the compatibility pack shows it's for Office 2000 and later which means it won't work for Office97.
    Quote Quote  
  15. Member
    Join Date
    Nov 2004
    Location
    Las Vegas
    Search Comp PM
    bevills1,

    If you've already decided to just update to a newer version of excel, (since you already have excel 97) you should keep in mind that you can't use the "upgrade" version of excel 2007 from excel 97, and if you want to use a "upgrade" version of excel 2003, it has to ugrade to the "professional" or "enterprise" version. Anything below professional (in excel 2003) still doesn't natively output to xml, although it sounds like you might be able to use that compatibility pack to accomplish the same thing.

    If you would rather keep the ~$150 to ~$350 in your pocket, it might be worth it to look on the web for an excel 97 macro, xla, or xll that would get you to the same place. If it's even a semi-common problem you're facing, it's likely someone has already addressed it, and made the solution available. This isn't exactly rocket science, so there are probably quite a few people with the skills required. All an excel programmer would need is a copy of the workbook (xls) you're starting with, and a copy of what the output xml should be. It doesn't have to be elaborate...just format the output correctly...and from just one specific workbook (data input) to one specific xml format (output) file. Even if the programmer isn't familiar with spreadsheetml, ecma-376 is readily (and freely) available to resolve any problems they would encounter, google should give an even more direct answer to specific questions about spreadsheetml, and it's likely the web has excel programming forums where spreadsheetml is discussed.
    Quote Quote  
  16. Does it work with OpenOffice?
    tgpo famous MAC commercial, You be the judge?
    Originally Posted by jagabo
    I use the FixEverythingThat'sWrongWithThisVideo() filter. Works perfectly every time.
    Quote Quote  
  17. Banned
    Join Date
    Oct 2004
    Location
    Freedonia
    Search Comp PM
    Originally Posted by stiltman
    Does it work with OpenOffice?
    For those of you who don't know, let me introduce you to Stiltman, the guy with the infantile sense of humor.

    Some time earlier this year, perhaps a couple of months ago, we had a post in one forum where some guy listed a software program he said he used. I never heard of it. I said that I couldn't find any information about any such program on the internet when I did a search on it. Of course, foolish me, I was talking about such software being used for VIDEO EDITING, but leave it to my P.A.L. (figure out what that means) Stiltman who chimes in that a program with that exact same name exists for playing chess via the internet. So although his post in no way helped the original poster, he just had to make the point that a program somewhere on the internet did exist by that name, even though it had nothing to do with the subject at hand.

    So now we have more of him being an ass where for reasons known only to himself, he posts a quote from jagabo that has nothing to do with this thread to make a cheap joke. You are pathetic, Stiltman.
    Quote Quote  
  18. VH Wanderer Ai Haibara's Avatar
    Join Date
    Jan 2006
    Location
    Somewhere on VideoHelp...
    Search Comp PM
    Umm... I could be wrong, but hasn't that been stiltman's sig for a while?
    If cameras add ten pounds, why would people want to eat them?
    Quote Quote  
  19. Originally Posted by Ai Haibara
    Umm... I could be wrong, but hasn't that been stiltman's sig for a while?
    Yeah, jman98 has just been iching to get back at me. I even left it up to him to continue with the name calling.
    jman98, you're a complete moron aren't you.

    Look, bevills1, is having problems with a new xls file created in office 2007 (my formal guess)
    So instead of buying 200x to upgrade from 97, I suggested a free alternative OpenOffice to possibly open the file and save it back as an older version of MS office

    And yes, that quote has been in my SIGNATURE for a while

    Edit:
    OK, again, I will leave it up to you if you want to continue with the name calling....I know you must be feeling pretty peaved now that you realized your mistake

    Edit2:
    To the OP, take a look here at the bottom of the page. OpenOffice supports xlsx
    http://www.openoffice.org/product/calc.html
    Might be the ticket just to install it to convert the template
    tgpo famous MAC commercial, You be the judge?
    Originally Posted by jagabo
    I use the FixEverythingThat'sWrongWithThisVideo() filter. Works perfectly every time.
    Quote Quote  
  20. Member AlanHK's Avatar
    Join Date
    Apr 2006
    Location
    Hong Kong
    Search Comp PM
    Originally Posted by bevills1
    No, jman98, Excel2003 works as stated in my original post, but Excel97 doesn't work making it necessary to buy either Office2003 or Office 2007. Also the link for the compatibility pack shows it's for Office 2000 and later which means it won't work for Office97.
    According the the Wikipedia text, it can work stand alone with Excel 97, which I noted with the link.
    Did you try?

    But though it may be able to convert a file, it probably will not be able to execute a macro.

    Anyway, it's absurd that you have to buy an expensive program just to submit taxes.

    Have you complained directly to the tax office?

    They may have a workaround, maybe you can just submit an XLS file.

    Contact a local Linux Users' Group, they may have a free solution, and may be interested in lobbying the government to stop it imposing a "Microsoft Tax". Or the Electronic Frontier Foundation.
    Quote Quote  
  21. Member
    Join Date
    Jan 2006
    Location
    United States
    Search Comp PM
    As stated in my last Jun22 post and again in Jun23 post Open Office doesn't work. My state department of revenue does offer option to pay by US mail, but there's a $130 discount if done electronically. Thus it's more economical to buy newer Office version rather than shell out an extra $130 every year.

    This xls/xml stuff is all new to me, and I don't know but what clicking the "Create xml file" may be executing a Macro instead of simply converting xls to xml. From Wikipedia link given I didn't see where is stated it could work with Excek97 and only saw it stated for Office 2000 and later versions. It likely wouldn't work anyway if it's executing a Macro as opposed to simply converting xls to xml that I'd thought it was doing.
    Quote Quote  
  22. Member AlanHK's Avatar
    Join Date
    Apr 2006
    Location
    Hong Kong
    Search Comp PM
    Originally Posted by bevills1
    From Wikipedia link given I didn't see where is stated it could work with Excek97

    "The compatibility pack can also be used as a stand-alone converter with Microsoft Office 97."

    Wikipedia is no guarantee, but that's what it says.

    Originally Posted by bevills1
    likely wouldn't work anyway if it's executing a Macro
    You seem to have decided you want the new Office and are discounting any other suggestions without bothering to try them. Fine, just do it then.
    Quote Quote  
  23. I know you said you couldn't open the already converted xlsx to xml file done on your friends computer. However OpenOffice will open xlsx files.

    On second thought, you said it was a template. Does the original file have xltx extension or xlsx extension?

    OpenOffice 3.0.1 should be able to open both
    tgpo famous MAC commercial, You be the judge?
    Originally Posted by jagabo
    I use the FixEverythingThat'sWrongWithThisVideo() filter. Works perfectly every time.
    Quote Quote  
  24. Member
    Join Date
    Jan 2006
    Location
    United States
    Search Comp PM
    It's xls file as the file name is sampleuploadfile.xls, and Open Ofice can open it. However, nothing happens when the "Create xml file" is clicked after the file is open. This leads me to believe it's likely trying to execute a Macro as suggested by AlanHK and may not work for the same reason Excel97 probably can't execute a Macro. BTW I revisited the link in the AlanHK Jun23 post and am still unable to find where it says the compatibility pack can be used for Excel97.
    Quote Quote  
  25. Member AlanHK's Avatar
    Join Date
    Apr 2006
    Location
    Hong Kong
    Search Comp PM
    Originally Posted by bevills1
    I revisited the link in the AlanHK Jun23 post and am still unable to find where it says the compatibility pack can be used for Excel97.
    http://en.wikipedia.org/wiki/Open_Office#Microsoft_Office_interoperability

    And is this what you're trying to make:
    http://www.state.ar.us/dfa/excise_tax_v2/st_instructions.html

    Because if so it describes the format of a CDF text file which you can easily create in any version of Excel.
    Quote Quote  
  26. Member
    Join Date
    Jan 2006
    Location
    United States
    Search Comp PM
    What I'm trying to create is probably similar to that, but it's for sales tax for a different state. I'm unable to view it currently since I removed Office97. On second thought I wouldn't be able to view it anyway since xml is foreign to Office97.

    I downloaded and installed the compatibility pack from http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=941B3470-3AE9-...3-C6BB74CD1466 I think. It said install was complete, but the program isn't found in Programs or any where else when a search is done, and it's not seen in the list to Add/Remove Programs. Now the question arises, did it really install, and if so, am I now possessed by another Microsoft demon requiring exorcism?


    Edit: It's the "Click Here to download a copy of the Excel Template. " link on the right about half way down the page at http://www.gataxinfo.org/ if anyone wants to play with it to see what might work.
    Quote Quote  
  27. Member
    Join Date
    Nov 2004
    Location
    Las Vegas
    Search Comp PM
    bevills1,

    This xls/xml stuff is all new to me, and I don't know but what clicking the "Create xml file" may be executing a Macro instead of simply converting xls to xml.
    If the file extension is xls, it's an excel workbook...which has one or more worksheets, and may or may not have macros included within the workbook.

    If the file extension is xml, it's a file which is based on extensible markup language. An xml file could contain almost anything, but still relies on the xml model for internal formatting (how data is stored/represented/identified). A functional relative of xml which you may be familiar with is HTML (hypertext markup language). Excel (2003 professional edition and above) can output a very specific type of xml file which is based on microsoft's "spreadsheetml" (spreadsheet markup language).

    If you click on an excel worksheet, and an action is performed, what performs the task is a macro. A macro executes a series of instructions (like a program does), but since it only works inside excel (in this case) it's more like a subroutine or function in a dll than a standalone executable. Macros can be local to a given workbook, shared in the default Personal.xls, or in a xla or xll. Macros add capabilities to excel that don't exist natively in excel. One common purpose for macros is the processing of forms, which is probably what this poorly implemented workbook in your possession is doing.

    When you're clicking "Create xml file", a macro is taking control of the processing...that's the only way it can be. At this point, all we know for sure is the called macro (or another macro called from the original macro) is encountering a problem that results in the error message "Compile error: Sub or function not defined". In other words, the macro is unable to complete its' task because a subroutine or function being called isn't available. Without seeing the line where the macro is failing, it can't be said with certainty, but if the macro works in excel 2003 and not in excel 97, it's reasonable to assume the problem is being encountered during the output of the processed data.

    If that's the case, there's no reason the troublesome macro (in the workbook you are trying to use) can't be modified, at the point where it's failing, to call a different method to handle output, which would allow the macro to complete it's mission. You would use the modified version of the workbook (in excel 97) in exactly the same way you would use the unmodified workbook in excel 2003 (professional or above), and if properly done, the output file would be identical either way.

    The second paragraph of my previous post addresses how this would be done, and describes what you're looking for. It would be better to first try that route...only you have the details which would yield the specific keywords google needs, and for the rest of us, it's like looking for a needle in a haystack.

    Like greymalkin, I would be very surprised if excel macros would work in Open Office Calc...that just doesn't sound like microsoft.
    Quote Quote  
  28. Member
    Join Date
    Jan 2006
    Location
    United States
    Search Comp PM
    I'd suspect Excel97 would be equally unlikely to work with macros involving xml file creation if Excel97 doesn't know anything about xml as AlanHK says. I think my only option is to just buy a newer Microsoft Office version.
    Quote Quote  
  29. here's the macro

    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 4/22/2009 by william shepard
    '

    '
    ActiveWindow.SmallScroll Down:=6
    Range("B32").Select
    Sheets("Sheet1").Select
    Rows("11:11").Select
    Selection.Insert Shift:=xlDown
    Range("A10:C10").Select
    Selection.Copy
    Range("A11:C11").Select
    ActiveSheet.Paste
    Sheets("Master filer-State and Atlanta ").Select
    Range("B32").Select
    End Sub
    Sub Macro3(A1)
    '
    ' Macro3 Macro
    ' Macro recorded 4/22/2009 by william shepard
    '

    '
    Sheets("Master Location Summary").Select
    ActiveSheet.Unprotect Password:="nom5202"
    Range("A32:N32").Offset(A1, 0).Select
    Selection.Copy
    Range("A33:N33").Offset(A1, 0).Select
    Selection.Insert Shift:=xlDown
    ActiveSheet.Protect Password:="nom5202", DrawingObjects:=True, Contents:=True, Scenarios:=True
    Sheets("Master filer-State and Atlanta ").Select
    Range("B37").Select
    End Sub
    tgpo famous MAC commercial, You be the judge?
    Originally Posted by jagabo
    I use the FixEverythingThat'sWrongWithThisVideo() filter. Works perfectly every time.
    Quote Quote  
  30. Member
    Join Date
    Nov 2004
    Location
    Las Vegas
    Search Comp PM
    stiltman,

    From what you posted, I was able to locate a workbook which is probably the same as what you're looking at. If that's the case, what you posted is just the tip of the iceberg, and not directly connected to the problem. If you unprotect the summary worksheet of that workbook (the password is given in "Macro3"), and open it in the vba editor, the macro code for the "create xml" button is in module 1. The problem should originate in that macro.

    If this is the problem workbook, it would save time to know the line where execution halts in excel 97. It's not completely necessary, but it would save time over having to piece together how the whole thing works.
    Quote Quote  



Similar Threads

Visit our sponsor! Try DVDFab and backup Blu-rays!