Can worksheet data be exported/imported to/from flat file?

We have a software product which does structured data storage and retrieval
of application data to and from the database. (Like almost any other
application - nothing new here.) To allow customers to do ad-hoc extensions,
we have a module which allows them to integrate their own spreadsheets.
Alternatively, the client may contract with us to develop custom
spreadsheets for them. This allows us to quickly integrate custom
'interfaces' with our app, according to each customer's needs.

The problem with this, is that the 'code' (in our context VBA) is stored
repetitively with each document saved. With the number of documents
typically approaching the thousands at most customers, correcting a bug, or
evolving the VBA becomes a real problem. Only new documents will benefit fom
bug fixes / code evolution. Existing documents will continue to run exactly
as they always have.

So I have this idea, whereby I'd like to continue using Excel in the current
manner, but instead of storing the entire worksheet (data and code) I'd like
to somehow separate the data and save only the data. Like an export
function. opening a document would involve opening the relevant worksheet
(which is actually just a code template), and merging in the data which was
exported at the end of the last editing session. That way, code changes
would impact all documents, including legacy documents. (Of course new
functions would need to account for backward compatibility, but that's
nothing new, it's as it should be.)

So is there any defined way to simply export / import all spreadsheet data
without actually knowing anything about the details of the spreadsheet? Or
will I have to write a custom import / export function for every
spreadsheet?

Thanks for your help,

- Joe Geretz -


0
jgeretz (30)
11/27/2003 12:45:27 AM
excel 39879 articles. 2 followers. Follow

6 Replies
724 Views

Similar Articles

[PageSpeed] 33

You can manually copy or move 1, 2 3 or all worksheets to a new workboo
by right clicking on the sheet name tab and following the promts. Thi
will copy any macro codes that are on those sheet but will not copy an
code in Forms or Modules

I hope this is what you are after and I did not misunderstand you
quer

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

0
11/27/2003 1:16:54 AM
The command:

Worksheets.Copy

will copy all the worksheets in the active workbook to a new, uinnamed
workbook, without the code. Of course, any code in worksheet modules will be
copied, but standard modules will not.

-- 

Vasant

"Joseph Geretz" <jgeretz@nospam.com> wrote in message
news:%23qD%23D$HtDHA.2880@tk2msftngp13.phx.gbl...
> We have a software product which does structured data storage and
retrieval
> of application data to and from the database. (Like almost any other
> application - nothing new here.) To allow customers to do ad-hoc
extensions,
> we have a module which allows them to integrate their own spreadsheets.
> Alternatively, the client may contract with us to develop custom
> spreadsheets for them. This allows us to quickly integrate custom
> 'interfaces' with our app, according to each customer's needs.
>
> The problem with this, is that the 'code' (in our context VBA) is stored
> repetitively with each document saved. With the number of documents
> typically approaching the thousands at most customers, correcting a bug,
or
> evolving the VBA becomes a real problem. Only new documents will benefit
fom
> bug fixes / code evolution. Existing documents will continue to run
exactly
> as they always have.
>
> So I have this idea, whereby I'd like to continue using Excel in the
current
> manner, but instead of storing the entire worksheet (data and code) I'd
like
> to somehow separate the data and save only the data. Like an export
> function. opening a document would involve opening the relevant worksheet
> (which is actually just a code template), and merging in the data which
was
> exported at the end of the last editing session. That way, code changes
> would impact all documents, including legacy documents. (Of course new
> functions would need to account for backward compatibility, but that's
> nothing new, it's as it should be.)
>
> So is there any defined way to simply export / import all spreadsheet data
> without actually knowing anything about the details of the spreadsheet? Or
> will I have to write a custom import / export function for every
> spreadsheet?
>
> Thanks for your help,
>
> - Joe Geretz -
>
>


0
Vasant
11/27/2003 5:11:19 AM
As a suggestion, look at saving the workbook as XML Data or an XML Spreadsheet.

--

"Joseph Geretz" <jgeretz@nospam.com> wrote in message news:<#qD#D$HtDHA.2880@tk2msftngp13.phx.gbl>...
> We have a software product which does structured data storage and retrieval
> of application data to and from the database. (Like almost any other
> application - nothing new here.) To allow customers to do ad-hoc extensions,
> we have a module which allows them to integrate their own spreadsheets.
> Alternatively, the client may contract with us to develop custom
> spreadsheets for them. This allows us to quickly integrate custom
> 'interfaces' with our app, according to each customer's needs.
> 
> The problem with this, is that the 'code' (in our context VBA) is stored
> repetitively with each document saved. With the number of documents
> typically approaching the thousands at most customers, correcting a bug, or
> evolving the VBA becomes a real problem. Only new documents will benefit fom
> bug fixes / code evolution. Existing documents will continue to run exactly
> as they always have.
> 
> So I have this idea, whereby I'd like to continue using Excel in the current
> manner, but instead of storing the entire worksheet (data and code) I'd like
> to somehow separate the data and save only the data. Like an export
> function. opening a document would involve opening the relevant worksheet
> (which is actually just a code template), and merging in the data which was
> exported at the end of the last editing session. That way, code changes
> would impact all documents, including legacy documents. (Of course new
> functions would need to account for backward compatibility, but that's
> nothing new, it's as it should be.)
> 
> So is there any defined way to simply export / import all spreadsheet data
> without actually knowing anything about the details of the spreadsheet? Or
> will I have to write a custom import / export function for every
> spreadsheet?
> 
> Thanks for your help,
> 
> - Joe Geretz -
0
onedaywhen (103)
11/27/2003 4:57:46 PM
Hi Guys,

Thanks for your suggestions.

> As a suggestion, look at saving the workbook as XML Data or an XML
Spreadsheet.

Sounds interesting, I've never heard of this. Got a link where I can find
out more?

Thanks,

- Joe Geretz -

> --
>
> "Joseph Geretz" <jgeretz@nospam.com> wrote in message
news:<#qD#D$HtDHA.2880@tk2msftngp13.phx.gbl>...
> > We have a software product which does structured data storage and
retrieval
> > of application data to and from the database. (Like almost any other
> > application - nothing new here.) To allow customers to do ad-hoc
extensions,
> > we have a module which allows them to integrate their own spreadsheets.
> > Alternatively, the client may contract with us to develop custom
> > spreadsheets for them. This allows us to quickly integrate custom
> > 'interfaces' with our app, according to each customer's needs.
> >
> > The problem with this, is that the 'code' (in our context VBA) is stored
> > repetitively with each document saved. With the number of documents
> > typically approaching the thousands at most customers, correcting a bug,
or
> > evolving the VBA becomes a real problem. Only new documents will benefit
fom
> > bug fixes / code evolution. Existing documents will continue to run
exactly
> > as they always have.
> >
> > So I have this idea, whereby I'd like to continue using Excel in the
current
> > manner, but instead of storing the entire worksheet (data and code) I'd
like
> > to somehow separate the data and save only the data. Like an export
> > function. opening a document would involve opening the relevant
worksheet
> > (which is actually just a code template), and merging in the data which
was
> > exported at the end of the last editing session. That way, code changes
> > would impact all documents, including legacy documents. (Of course new
> > functions would need to account for backward compatibility, but that's
> > nothing new, it's as it should be.)
> >
> > So is there any defined way to simply export / import all spreadsheet
data
> > without actually knowing anything about the details of the spreadsheet?
Or
> > will I have to write a custom import / export function for every
> > spreadsheet?
> >
> > Thanks for your help,
> >
> > - Joe Geretz -


0
jgeretz (30)
11/27/2003 10:42:59 PM
File, Save As, Save as type = XML Spreadsheet (XML Data option is
xl2003 only). Then open the .xml file in a HTML editor etc.

--

"Joseph Geretz" <jgeretz@nospam.com> wrote in message news:<uwBqSfTtDHA.700@TK2MSFTNGP11.phx.gbl>...
> Hi Guys,
> 
> Thanks for your suggestions.
> 
> > As a suggestion, look at saving the workbook as XML Data or an XML
> Spreadsheet.
> 
> Sounds interesting, I've never heard of this. Got a link where I can find
> out more?
> 
> Thanks,
> 
> - Joe Geretz -
> 
> > --
> >
> > "Joseph Geretz" <jgeretz@nospam.com> wrote in message
>  news:<#qD#D$HtDHA.2880@tk2msftngp13.phx.gbl>...
> > > We have a software product which does structured data storage and
>  retrieval
> > > of application data to and from the database. (Like almost any other
> > > application - nothing new here.) To allow customers to do ad-hoc
>  extensions,
> > > we have a module which allows them to integrate their own spreadsheets.
> > > Alternatively, the client may contract with us to develop custom
> > > spreadsheets for them. This allows us to quickly integrate custom
> > > 'interfaces' with our app, according to each customer's needs.
> > >
> > > The problem with this, is that the 'code' (in our context VBA) is stored
> > > repetitively with each document saved. With the number of documents
> > > typically approaching the thousands at most customers, correcting a bug,
>  or
> > > evolving the VBA becomes a real problem. Only new documents will benefit
>  fom
> > > bug fixes / code evolution. Existing documents will continue to run
>  exactly
> > > as they always have.
> > >
> > > So I have this idea, whereby I'd like to continue using Excel in the
>  current
> > > manner, but instead of storing the entire worksheet (data and code) I'd
>  like
> > > to somehow separate the data and save only the data. Like an export
> > > function. opening a document would involve opening the relevant
>  worksheet
> > > (which is actually just a code template), and merging in the data which
>  was
> > > exported at the end of the last editing session. That way, code changes
> > > would impact all documents, including legacy documents. (Of course new
> > > functions would need to account for backward compatibility, but that's
> > > nothing new, it's as it should be.)
> > >
> > > So is there any defined way to simply export / import all spreadsheet
>  data
> > > without actually knowing anything about the details of the spreadsheet?
>  Or
> > > will I have to write a custom import / export function for every
> > > spreadsheet?
> > >
> > > Thanks for your help,
> > >
> > > - Joe Geretz -
0
onedaywhen (103)
11/28/2003 9:12:20 AM
It sounds like you shuld make your code into an Add-In.  Then your customer
installs the Add-In and your code is always accessible (it will load when
Excel loads).  You update the code in the add-in and release one file to the
customer.

The code will need to be modified slightly: you will have to change any
references to correctly refer to either ThisWorkbook for items in the sheets
of your add-in file (should be static data), or ActiveWorkbook for data in
sheets in the customer's files.  Pop-up context menus are not availble from
an add-in, so these functions will need to be put into regular menus or
toolbars.

The add-in could contain code to check for the macro in existing files and
copy the data to new files, as they are accessed.




------------8<--------------
> The problem with this, is that the 'code' (in our context VBA) is stored
> repetitively with each document saved. With the number of documents
> typically approaching the thousands at most customers, correcting a bug,
or
> evolving the VBA becomes a real problem. Only new documents will benefit
fom
> bug fixes / code evolution. Existing documents will continue to run
exactly
> as they always have.
>
> So I have this idea, whereby I'd like to continue using Excel in the
current
> manner, but instead of storing the entire worksheet (data and code) I'd
like
> to somehow separate the data and save only the data. Like an export
> function. opening a document would involve opening the relevant worksheet
> (which is actually just a code template), and merging in the data which
was
> exported at the end of the last editing session. That way, code changes
> would impact all documents, including legacy documents. (Of course new
> functions would need to account for backward compatibility, but that's
> nothing new, it's as it should be.)
>
> So is there any defined way to simply export / import all spreadsheet data
> without actually knowing anything about the details of the spreadsheet? Or
> will I have to write a custom import / export function for every
> spreadsheet?
>
> Thanks for your help,
>
> - Joe Geretz -
>
>


0
homer189 (5)
11/28/2003 3:18:28 PM
Reply:

Similar Artilces:

find action on log file
Hello there I want to use outside tool to find who made some update on table in my server I know that there are many tools for this. But can they do it on simple recovery model? Roy Goldhammer (royg@yahoo.com) writes: > I want to use outside tool to find who made some update on table in my > server > > I know that there are many tools for this. But can they do it on simple > recovery model? No. If you are using the simple recovery model, the contents of the log is wasted away everyonce in a while. Well, if the disk area has not been overwritten...

Publisher file to PDF, image problems.
when I print a Publisher file to PDF, my logo comes out fuzzy. Michelle1031 <Michelle1031@discussions.microsoft.com> was very recently heard to utter: > when I print a Publisher file to PDF, my logo comes out fuzzy. If you zoom in on your document in Publisher, is your logo fuzzy? What PDF quality settings are you using? -- Ed Bennett - MVP Microsoft Publisher Michelle, I have tried that b4 and had no success. I even had a DTP specialist try it, it still does not work. The only thing I can suggest is to insert the photo from ur computer into the document after you conve...

File size #11
I have read the other discussions on file sizes but they do not seem to address my problem. I have an Excel file that is 12mb large with low-res jpegs in it. This file also has merged cells to make it look pretty. Does Excel look at these merged cells as graphics? Is this why they are too big? I have run a macro to make sure that it goes to the last cell. How can I get the file smaller? How big are the graphics? If you remove them from the file, what is the size of the file and what is the size of the graphic files? To be sure you do not have extra formatting, if you open the file...

circular files; iterating to a "correct" solution
I am working with a circular file. I have been making changes to the file, and I have noticed at times when i manually calculate that the file iterates to an obviously "incorrect" solution. No amount of changing the max no of iterations or the max change seems to fix the problem. It iterates to the same "incorrect" solution every time. It is like it is stuck in a rut and can't get out. I have also noticed that, if I manually calculate each worksheet separately (that is, hit shift - f9 simulaneously on each worksheet individually, from left to right) all the ...

Maximum file sizes
Is there a recommended maximum file size for Excel 2000. PC spec 2Ghz P4 with 256 Mb Any advice appreciated Deus -------------- Does Not Exist Hi have a look at http://www.decisionmodels.com/memlimits.htm -- Regards Frank Kabel Frankfurt, Germany "Deus DNE" <deus.dne@ntlworld.com> schrieb im Newsbeitrag news:1561701c41d4f$358950f0$a001280a@phx.gbl... > Is there a recommended maximum file size for Excel 2000. > > PC spec 2Ghz P4 with 256 Mb > > Any advice appreciated > > Deus > -------------- > Does Not Exist ...

Changing Cells and entering data in them
Thanks for the help again. Big thanks to Steve you've got me this far. I went out and bought a book, but it's like reading a foreign language. I was informed today that I can't have message boxes come up. I need to have the code point at the cells and if they are blank turn which ever one is blank red or if both are then both turn red then pause for each cell to be filled in. Cell F14 "Last Name" then automatically go to Cell F16 "First Name" on tab or enter. Basically if Cell F22 or F23 has an X in it, Cells F14 an F16 turn red and cell F14 has the focus...

Using Relative path for XML data file?
Is there a way to specify a relative path to an XML data file imported into Excel 2003? I am writing a web app that generates report data as XML for the user to download to their local machine. This data is to be consumed by an Excel reporting spreadsheet, which contains display-formatted tables and charts that are mapped to various data fields in an XML Map, which is in turn linked to the xml data file they will download. The idea is the user only needs to download the data for updates, not the whole spreadsheet. However, since I cannot predict the path where the user will store their...

How to repair a .dll file in IE8
Several days ago I noticed in my Dependency Walker that the IESHIMS.dll files has a yellow circle with a question mark on it. What does this mean and How do I repair it? OS: Windows Vista Home Premium Browser Internet Explorer 8 -- TW Hi, See the History tab on that dialog. A web search for ieshims.dll files will also help you find a solution for that file. Regards. "TW" <TW@discussions.microsoft.com> wrote in message news:63E61463-D766-4ABC-B081-BFA8C04FB159@microsoft.com... > Several days ago I noticed in my Dependency Walker that the IESHIMS....

Linking files 2 ways
I have a work book that is linked to another and vise versa. As thus: Workbook A is where the input of data is made; Workbook B has a link to the input from workbook A; Workbook A retrieves the altered data back as a link. Although this all works fine with both books open, I note that if I open workbook A by itself, that the data it retrieves from Workbook B is not updated . If However, both books are open, there's no problem. I thought linked books were updated automatically if the Update remote references has been selected?? But it appears that the second book is not updated until it ...

OL2007 not move big files from outbox to sent
Hi, We have 2 computers with separate email accounts on Roadrunner. One machine has XP with Outlook 2002-sp3 and works without any problems. The other has Outlook 2007 on Vista and has problems sending files over a meg or so in size. It seems to actually send the file but the file remains in the outbox folder and does not move it to the sent folder. I say it "seems" to send the file because some people complain of getting muliple copies and others don't seem to get them at all. If I hit send again (not set up for auto send) it seems to send the file again (why some ...

unsolicited entry in the folder "Temporary Internet Files"
Hello, I am working on a programme which browses web sites and runs under XP. The http download is as follows: pServer = Isession -> GetHttpConnection(strServerName, nPort); pFile = pServer->OpenRequest(CHttpConnection::HTTP_VERB_GET, strObject, NULL, 1, NULL, NULL, dwHttpRequestFlags); pFile->SendRequest(); pFile->QueryInfoStatusCode(dwStatusCode); if(dwStatusCode == 200) { pFile -> QueryInfo(HTTP_QUERY_LAST_MODIFIED, &sysT); status.lastMod = sysT; if(DBlastMod == status.lastMod) //URL content has not changed since the last visit ...

RMS V2 and QSC Import
I have successfully imported several of my customers products using the QSC Import tool as a test but am unable to import the items with any association with sales tax? The default Sales Tax is TAX which is what I plan on using but once imported it creates a new item code TAX so now I have two? I plan on importing several hundred products and any help would be much appreciated. -Jess Reference- https://mbs.microsoft.com/partnersource/products/rms/downloads/releases/rms_so_importutility.htm i use the same tool but i dont tell the import utility anything about tax. import the items witho...

data sort
ok now should be simple >> I need to sort by month on data that is held in format >> day/month so eg 1511 1510 3011 3010 now custom/ends with/ 11... does not work custom/ends with/ ??11.. or *11 does not work either contains 11 does not work (& would also be wrong if data set contained 1011) but still I am stumped so any help would be great cheers Alex I would be inclined to add a new, temporary field of formulas that pull off the right 2 digits, and sort by that: =RIGHT(A1,2) -- Jim Rech Excel MVP ...

Is anyone an expert with outlook that I can call on the phone?
How do I share calenders between outlook and my MSN Premier account? Assuming you are using the outlook connector, you need to set the msn account to be the default message store then outlook will use the msn calendar as the default. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM You can access this...

Pulling data from separate tabs
When charting in Excel 2002 is there a way to use sets of data from two different tabs within the same worksheet? For example, a spreadsheet contains separate tabs for prior year and current year data. Is there a way to reference the data or label series to pick up data from both? I tried pointing and clicking, and then typing the following as a reference for the axis labels: ='Prior Year'!$B$110:$M$110,'Current Year'!'$B$110:$M$110 but receive an error that I'm referring to an external worksheet. I've used the comma (') in the past to reference breaks ...

How to automate increasing the form cache registry/file etc...
I want to roll out a batch file to make a number of tweaks to CRM The body of it would go REGEDIT /S Kerberosefix.reg REGEDIT /S ForceFormreload.reg REGEDIT /S OutlookFix.reg It would also rename OSA.exe to OSA.bad Remove OSA.exe From the startup menu I need help finding a way to use my batch file to increase the Outlook Form cache from the default 4MB to 50 MB.. This makes CRm more stable and faster for communications. I dont want to manually do this, as it time consuming, are my end users would not be reliable in doing it themselves. I also want to make another batch file or button that...

Math Worksheet Database
I am interested in developing/acquiring an Access 2003 database to generate (K-5 grade) math worksheets where I can track student results. I would hope not to reinvent the wheel. Thank you for any assistance. ...

CSV Files and VLOOKUP error
Does anyone know why VLOOKUP and Compare formulas don't work o information originating from a CSV file? I've tried copying an pasting values only (to leave behind any formatting), but it doesn' help. Through countless tests, I've narrowed it down to the CSV file bein the only possible cause -- Message posted from http://www.ExcelForum.com Hi ajpowers, Just a guess but the imported data may have leading or trailing spaces or are numbers stored as text. You could use the formula =A1=D1 to see if you get a true or false, where A1 is the lookup value and D1 ia the CVS valu...

How to import images from Publisher into Picture Manager?
I have images in Publisher that I need to import in to Microsoft office picture manager. How do I do this? Find the source of those images and save them. -- JoAnn Paules MVP Microsoft [Publisher] "Heatherwithaheadache" <Heatherwithaheadache@discussions.microsoft.com> wrote in message news:B1B1DFC9-0639-49BA-8614-A5AC0DDB596D@microsoft.com... >I have images in Publisher that I need to import in to Microsoft office > picture manager. How do I do this? Right-click the picture, save as picture, or copy/paste to a folder. The Picture Manager will search for al...

predict future data
Is there a way to create an XY line graph wih plotted data, yet leave room to predict future data on the axes? I can get the graph, but the x and y axes stop at the last data points, and I want those axes continued so that the existing data can be examined and future data predicted and plotted on the same graph, but I am not sure how to accomplish this. Any suggestions would be appreciated. Thanks. Jeff 1) Click on data series in chart, use Add trendline; in Option tab specify some units forward OR 2) Read Help about TREND and FORECAST, and SLOPE and INTERCEPT OR 3) Get crystal bal...

Can I share entities in migration process?
The entities in CRM have only one owning user. If I want=20 who a user see a entity of another user, the entity must=20 be shared with this user.=20 Well, I want migrate entities who must be shared with some=20 users. Exists a way to do it? Thank you for pay attention and sorry my bad english. []'s Vin=EDcius Pitta Lima de Ara=FAjo ...

Formula for cross tab data filling
Hi All Excel 2003 How to using formula for data filling as below (Y/N) ? Sheet A Product A Product B Product C System A Y N Y System B Y Y N Sheet B System A Product A System A Product C System B Product A System B Product B moonhkt ...

Can't do adjustment in analytical accounting
I am trying to change an analytical assignmnet through the Edit Analysis. When I try and change it, I receive an error stating "The Code of this Transaction Dimension cannot be adjusted". Has anyone seen this before? I have done similar changes many times, but I don't know why I can't now. Any insight would be helpful. Thanks, KJ Hi KJ What version of Gp are you running? I haveseen this happen in 8 but one of the service packs fixed it. (I think it was SP 4 or 5) Fliehigh "KJ" wrote: > I am trying to change an analytical assignmnet through the Edit >...

Help with importing data
Can I have users fill in a form in Access and have that data be transferred and updated to a spreadsheet. Need for fill out several fields and then export to a specific spreadsheet and place that data into the cells that will update that cell (add to the total in that cell) of a spreadsheet. ...

How can I change text to proper text in multiple cells.
I need to change names that are all in caps to proper case in 100 cells. If I click each one individually, it works, but I need to be able to perfomr this funcion automatically on all the cells. One other post said to be sure calc is set to automatic and mine is. Any instruction is most appreciated. Thank you. Insert a helper column to the right of the column with the names. Then use a formula like: =proper(a1) and drag down that column Then select that column edit|copy select the original range Edit|paste special|Values And then delete the helper column. bethye99 wrote: > > I...