GreenTree
06-23-2007, 10:58 AM
I have a project which uses User Defined Types extensively, and I need to be able to store the data into a spreadsheet which I can save, and then retireve it from the same spreadsheet later. Initially, it takes several minutes to generate the data from a bunch of sources with a bunch of processing, so being able to store & retrieve it for the next time I need to use it is essential. The data types look something like the following (somewhat simplified for illustrative purposes):
Type JobType
StartTime as integer ' minutes since midnight
StopTime as integer
Cost as double
Description as string
End Type
Type DayType
NumberJobs as integer ' may be one, may be as many as 10 jobs in a day
DayCost as double
DayRemarks as string
Jobs (1 to 10) as JobType
End Type
Type ProjectType
NumberDays as integer ' projects are from one to four days long
ProjectCost as double
BillingCode as integer
ProjectRemarks as string
Days(1 to 4) as DayType
end Type
Type MonthType
NumberOfProjects as integer 'from 3 to 15 projects in a month
MonthCost as double
MonthCashFlow as double
HoursBilled as double
Projects (1 to 15) as ProjectType
End Type
Then the variable that holds everthing would be
Public Year(1 to 12) as MonthType
Up until now, I've been saving everything in a spreadsheet using a lot of counters & For-Next loops to put everything into the cells and then to read it back in when the spreadsheet reloads (or reinitializes). My experience with programming (Pascal, Basic, a little 6502 Assembly language, some Ada, if you remember that) is greater than my experience with VBA, and there may well be a slick & efficient way (with Variants??) to put this stuff into a spreadsheet so that storing & reading it happens much quicker than running all the loops. Am I right in thinking that there is, or am I barking up the wrong tree?
Many thanks!
Type JobType
StartTime as integer ' minutes since midnight
StopTime as integer
Cost as double
Description as string
End Type
Type DayType
NumberJobs as integer ' may be one, may be as many as 10 jobs in a day
DayCost as double
DayRemarks as string
Jobs (1 to 10) as JobType
End Type
Type ProjectType
NumberDays as integer ' projects are from one to four days long
ProjectCost as double
BillingCode as integer
ProjectRemarks as string
Days(1 to 4) as DayType
end Type
Type MonthType
NumberOfProjects as integer 'from 3 to 15 projects in a month
MonthCost as double
MonthCashFlow as double
HoursBilled as double
Projects (1 to 15) as ProjectType
End Type
Then the variable that holds everthing would be
Public Year(1 to 12) as MonthType
Up until now, I've been saving everything in a spreadsheet using a lot of counters & For-Next loops to put everything into the cells and then to read it back in when the spreadsheet reloads (or reinitializes). My experience with programming (Pascal, Basic, a little 6502 Assembly language, some Ada, if you remember that) is greater than my experience with VBA, and there may well be a slick & efficient way (with Variants??) to put this stuff into a spreadsheet so that storing & reading it happens much quicker than running all the loops. Am I right in thinking that there is, or am I barking up the wrong tree?
Many thanks!