What's the best way to...
 

Hi. I think this is possible, but I don't know how to go about it or where
exactly to look.

I have a multi-worksheet workbook that is around 2.4 mb in size. The user
inputs a relatively small amount of information on a few of the worksheets.
The rest of the worksheets hold fixed data used for performing various
calculations or building reports. For each separate use, the user is
performing a 'Save As'. I have one user who is creating up to 20 of these
files each day which is really filling up their hard-drive.

I'd like to have a solution to this problem and I'm guessing that if all of
the 'input' cells were to be exported to a comma delimited text file, the
text file would be very small in comparison. Likewise, if the text file were
imported, all of the calculations and print functions would still work
normally.

So, my question is: How do I go about this?

I'm sure the solution is VBA, but my VBA knowledge is very slim.

Any and all help is much appreciated. TIA :-)

--
rpw

Hi again,

I've got an idea for the 'export' side of the problem. I've created another
worksheet for data collection where each cell equals one of the other
worksheet's 'input' cells. I'll then have a 'Save As .csv" routine run so
the data collection sheet will be saved. I'm working on building this now
and I don't think this step will be any problem.

However, I don't quite know how to go about populating the blank template
from one of the saved csv files. Any help is appreciated.

Thanks in Advance

"rpw" wrote:

Hi. I think this is possible, but I don't know how to go about it or where
exactly to look.

I have a multi-worksheet workbook that is around 2.4 mb in size. The user
inputs a relatively small amount of information on a few of the worksheets.
The rest of the worksheets hold fixed data used for performing various
calculations or building reports. For each separate use, the user is
performing a 'Save As'. I have one user who is creating up to 20 of these
files each day which is really filling up their hard-drive.

I'd like to have a solution to this problem and I'm guessing that if all of
the 'input' cells were to be exported to a comma delimited text file, the
text file would be very small in comparison. Likewise, if the text file were
imported, all of the calculations and print functions would still work
normally.

So, my question is: How do I go about this?

I'm sure the solution is VBA, but my VBA knowledge is very slim.

Any and all help is much appreciated. TIA :-)

--
rpw

Questions in Column A.
Responses in column B.

Then when you save as .csv, you have essentially two columns.

Then you could either provide a macro that opens the .csv and copy|paste special
Values from the second column (of the .csv file) into the second column of your
input sheet.

But be careful. If you insert new rows (questions), you might be putting the
wrong response with the wrong question.

I think I'd add a first question: Version of Input Sheet.

Then you could build rules that say that question #8 is now question #10, and #8
and #9 will default to a new value.

So you'd have a conversion for each version (until a version gets old enough to
say that you're not supporting it any more.)

Good luck,

rpw wrote:

Hi again,

I've got an idea for the 'export' side of the problem. I've created another
worksheet for data collection where each cell equals one of the other
worksheet's 'input' cells. I'll then have a 'Save As .csv" routine run so
the data collection sheet will be saved. I'm working on building this now
and I don't think this step will be any problem.

However, I don't quite know how to go about populating the blank template
from one of the saved csv files. Any help is appreciated.

Thanks in Advance

"rpw" wrote:

Hi. I think this is possible, but I don't know how to go about it or where
exactly to look.

I have a multi-worksheet workbook that is around 2.4 mb in size. The user
inputs a relatively small amount of information on a few of the worksheets.
The rest of the worksheets hold fixed data used for performing various
calculations or building reports. For each separate use, the user is
performing a 'Save As'. I have one user who is creating up to 20 of these
files each day which is really filling up their hard-drive.

I'd like to have a solution to this problem and I'm guessing that if all of
the 'input' cells were to be exported to a comma delimited text file, the
text file would be very small in comparison. Likewise, if the text file were
imported, all of the calculations and print functions would still work
normally.

So, my question is: How do I go about this?

I'm sure the solution is VBA, but my VBA knowledge is very slim.

Any and all help is much appreciated. TIA :-)

--
rpw

--

Dave Peterson

raw wrote:

I have one user who is creating up to 20 of these
files each day which is really filling up their hard-drive.

You can't "legislate" for this. The question has to be "WHY" does the
user need to change this file 20 times a day, and WHY can they not
either delete some of the old files or save them to a network drive
instead? What is so important about the number of times the file is
"saved as"?


--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

Hi Dave,

Thanks for responding so quickly.

The version idea/problem is one I didn't think about before. I'm now
including the version number in the auto-save file name. I don't expect too
many changes to the layout, but rather to the hidden data - prices and such.

I think also that if the user clicks the import button I'd want the macro to
check to see if there is data in the input cells and if there is then ask the
user if they'd like to save the existing first. If not then clear out any
existing data and import from a selected csv file. But those are things I'll
have to ponder out.

I have other questions regarding the columns in the csv now. My existing
sheet uses up to 12 columns in some rows and 3 or 4 in other rows. When I
look at the file with "Notebook", some of those rows end with a comma and
others don't and I don't know why. I'm thinking that I might have made a
mistake somewhere and should have just a single column. Do you have any
suggestions on where I might find some more info on this kind of thing?

thanks...

"Dave Peterson" wrote:

Questions in Column A.
Responses in column B.

Then when you save as .csv, you have essentially two columns.

Then you could either provide a macro that opens the .csv and copy|paste special
Values from the second column (of the .csv file) into the second column of your
input sheet.

But be careful. If you insert new rows (questions), you might be putting the
wrong response with the wrong question.

I think I'd add a first question: Version of Input Sheet.

Then you could build rules that say that question #8 is now question #10, and #8
and #9 will default to a new value.

So you'd have a conversion for each version (until a version gets old enough to
say that you're not supporting it any more.)

Good luck,

rpw wrote:

Hi again,

I've got an idea for the 'export' side of the problem. I've created another
worksheet for data collection where each cell equals one of the other
worksheet's 'input' cells. I'll then have a 'Save As .csv" routine run so
the data collection sheet will be saved. I'm working on building this now
and I don't think this step will be any problem.

However, I don't quite know how to go about populating the blank template
from one of the saved csv files. Any help is appreciated.

Thanks in Advance

"rpw" wrote:

Hi. I think this is possible, but I don't know how to go about it or where
exactly to look.

I have a multi-worksheet workbook that is around 2.4 mb in size. The user
inputs a relatively small amount of information on a few of the worksheets.
The rest of the worksheets hold fixed data used for performing various
calculations or building reports. For each separate use, the user is
performing a 'Save As'. I have one user who is creating up to 20 of these
files each day which is really filling up their hard-drive.

I'd like to have a solution to this problem and I'm guessing that if all of
the 'input' cells were to be exported to a comma delimited text file, the
text file would be very small in comparison. Likewise, if the text file were
imported, all of the calculations and print functions would still work
normally.

So, my question is: How do I go about this?

I'm sure the solution is VBA, but my VBA knowledge is very slim.

Any and all help is much appreciated. TIA :-)

--
rpw

--

Dave Peterson

You can count how many cells have something in them with something like:

dim resp as long
if application.countif(worksheets("sheet1").range("b: b")) 0 then
resp = msgbox(prompt:="overwrite existing?",buttons:=vbyesno)
if resp = vbno then exit sub
end if

But those extra commas shouldn't hurt anything. When you bring open the .csv,
excel will ignore them.

I think that this comes close to describing your problem:
http://support.microsoft.com/default...b;EN-US;q77295
XL: Column Delimiters Missing in Spreadsheet Saved as Text

(It actually describes missing delimiter, but if some are "missing", maybe the
ones appearing are "extra".)

(But after you import your .csv file, you'll see that you didn't have to worry
about this.)

rpw wrote:

Hi Dave,

Thanks for responding so quickly.

The version idea/problem is one I didn't think about before. I'm now
including the version number in the auto-save file name. I don't expect too
many changes to the layout, but rather to the hidden data - prices and such.

I think also that if the user clicks the import button I'd want the macro to
check to see if there is data in the input cells and if there is then ask the
user if they'd like to save the existing first. If not then clear out any
existing data and import from a selected csv file. But those are things I'll
have to ponder out.

I have other questions regarding the columns in the csv now. My existing
sheet uses up to 12 columns in some rows and 3 or 4 in other rows. When I
look at the file with "Notebook", some of those rows end with a comma and
others don't and I don't know why. I'm thinking that I might have made a
mistake somewhere and should have just a single column. Do you have any
suggestions on where I might find some more info on this kind of thing?

thanks...

"Dave Peterson" wrote:

Questions in Column A.
Responses in column B.

Then when you save as .csv, you have essentially two columns.

Then you could either provide a macro that opens the .csv and copy|paste special
Values from the second column (of the .csv file) into the second column of your
input sheet.

But be careful. If you insert new rows (questions), you might be putting the
wrong response with the wrong question.

I think I'd add a first question: Version of Input Sheet.

Then you could build rules that say that question #8 is now question #10, and #8
and #9 will default to a new value.

So you'd have a conversion for each version (until a version gets old enough to
say that you're not supporting it any more.)

Good luck,

rpw wrote:

Hi again,

I've got an idea for the 'export' side of the problem. I've created another
worksheet for data collection where each cell equals one of the other
worksheet's 'input' cells. I'll then have a 'Save As .csv" routine run so
the data collection sheet will be saved. I'm working on building this now
and I don't think this step will be any problem.

However, I don't quite know how to go about populating the blank template
from one of the saved csv files. Any help is appreciated.

Thanks in Advance

"rpw" wrote:

Hi. I think this is possible, but I don't know how to go about it or where
exactly to look.

I have a multi-worksheet workbook that is around 2.4 mb in size. The user
inputs a relatively small amount of information on a few of the worksheets.
The rest of the worksheets hold fixed data used for performing various
calculations or building reports. For each separate use, the user is
performing a 'Save As'. I have one user who is creating up to 20 of these
files each day which is really filling up their hard-drive.

I'd like to have a solution to this problem and I'm guessing that if all of
the 'input' cells were to be exported to a comma delimited text file, the
text file would be very small in comparison. Likewise, if the text file were
imported, all of the calculations and print functions would still work
normally.

So, my question is: How do I go about this?

I'm sure the solution is VBA, but my VBA knowledge is very slim.

Any and all help is much appreciated. TIA :-)

--
rpw

--

Dave Peterson


--

Dave Peterson

Sorry for not getting back quickly. Thank you for the bit of code and the
link, they look like they'll help me alot. I'll post back if I need more
help. Thanks again.

"Dave Peterson" wrote:

You can count how many cells have something in them with something like:

dim resp as long
if application.countif(worksheets("sheet1").range("b: b")) 0 then
resp = msgbox(prompt:="overwrite existing?",buttons:=vbyesno)
if resp = vbno then exit sub
end if

But those extra commas shouldn't hurt anything. When you bring open the .csv,
excel will ignore them.

I think that this comes close to describing your problem:
http://support.microsoft.com/default...b;EN-US;q77295
XL: Column Delimiters Missing in Spreadsheet Saved as Text

(It actually describes missing delimiter, but if some are "missing", maybe the
ones appearing are "extra".)

(But after you import your .csv file, you'll see that you didn't have to worry
about this.)

rpw wrote:

Hi Dave,

Thanks for responding so quickly.

The version idea/problem is one I didn't think about before. I'm now
including the version number in the auto-save file name. I don't expect too
many changes to the layout, but rather to the hidden data - prices and such.

I think also that if the user clicks the import button I'd want the macro to
check to see if there is data in the input cells and if there is then ask the
user if they'd like to save the existing first. If not then clear out any
existing data and import from a selected csv file. But those are things I'll
have to ponder out.

I have other questions regarding the columns in the csv now. My existing
sheet uses up to 12 columns in some rows and 3 or 4 in other rows. When I
look at the file with "Notebook", some of those rows end with a comma and
others don't and I don't know why. I'm thinking that I might have made a
mistake somewhere and should have just a single column. Do you have any
suggestions on where I might find some more info on this kind of thing?

thanks...

"Dave Peterson" wrote:

Questions in Column A.
Responses in column B.

Then when you save as .csv, you have essentially two columns.

Then you could either provide a macro that opens the .csv and copy|paste special
Values from the second column (of the .csv file) into the second column of your
input sheet.

But be careful. If you insert new rows (questions), you might be putting the
wrong response with the wrong question.

I think I'd add a first question: Version of Input Sheet.

Then you could build rules that say that question #8 is now question #10, and #8
and #9 will default to a new value.

So you'd have a conversion for each version (until a version gets old enough to
say that you're not supporting it any more.)

Good luck,

rpw wrote:

Hi again,

I've got an idea for the 'export' side of the problem. I've created another
worksheet for data collection where each cell equals one of the other
worksheet's 'input' cells. I'll then have a 'Save As .csv" routine run so
the data collection sheet will be saved. I'm working on building this now
and I don't think this step will be any problem.

However, I don't quite know how to go about populating the blank template
from one of the saved csv files. Any help is appreciated.

Thanks in Advance

"rpw" wrote:

Hi. I think this is possible, but I don't know how to go about it or where
exactly to look.

I have a multi-worksheet workbook that is around 2.4 mb in size. The user
inputs a relatively small amount of information on a few of the worksheets.
The rest of the worksheets hold fixed data used for performing various
calculations or building reports. For each separate use, the user is
performing a 'Save As'. I have one user who is creating up to 20 of these
files each day which is really filling up their hard-drive.

I'd like to have a solution to this problem and I'm guessing that if all of
the 'input' cells were to be exported to a comma delimited text file, the
text file would be very small in comparison. Likewise, if the text file were
imported, all of the calculations and print functions would still work
normally.

So, my question is: How do I go about this?

I'm sure the solution is VBA, but my VBA knowledge is very slim.

Any and all help is much appreciated. TIA :-)

--
rpw

--

Dave Peterson


--

Dave Peterson

Hi Gordon,

Well let me see if I can explain this...

This particular computer is not networked and it has an older hard drive
that isn't measured in 'gigs'.

As far as the spreadsheet, it is a pricing tool. In the hidden sheets are
fixed prices for certain basic products/items to which there are then fixed
multipliers for applying to those prices. Then there are other accessory
items with fixed prices. The user inputs quantities of each of the basic
products and accessories that are used in up to four different areas of the
job, then a material is selected which applies a multiplier to the basic
prices.

This particular user's job is to double-check the (for lack of a better
term) invoices the salespeople submit with the sold jobs. Each job needs to
be saved as a separate file in case the re-pricing needs to be reviewed.

I want the 'master' file with the fixed data and formulas to remain
unchanged and the variable data for each job to be saved for review. In the
spreadsheet's current format, the entire spreadsheet is saved. I'm trying to
get things so that only the variable data is saved in a format that
facilitates easy review.

rpw

"Gordon" wrote:

raw wrote:

I have one user who is creating up to 20 of these
files each day which is really filling up their hard-drive.

You can't "legislate" for this. The question has to be "WHY" does the
user need to change this file 20 times a day, and WHY can they not
either delete some of the old files or save them to a network drive
instead? What is so important about the number of times the file is
"saved as"?


--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk