|
code
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
VB and ExcelHi,
I have a VB app that needs to export data via ADO to a pre-defined Excel 2003 spreadsheet (has captions/headers to describe the data). The spreadsheet has pre-defined cells for where the data goes. I also need it to be flexible to allow the user to modify the excel spreadsheet (move data around) without having to change my program. So, I cannot hard code these cell locations in my code (ws.Range("J4").Value = rs!value1). I thought about creating a template file that holds all the cell locations for the data which would allow the user the ability to move data around. I also looked into using pivot tables and XML however I do not like these solutions for this app. Any other ideas? Thanks will,
You can name your cells/ranges. So initially Range("J4").Name="Range_1" If the user moves it or other cell, Range(""Range_1) still points to the correct cell. But will you ensure the user does not muck thing up ? Why do they need to rearrange the data presentation ? And is Excel the best way to show it to them if you alrady have a VB app available with the data ? NickHK Show quoteHide quote "will" <will@hasenoughspam.com> wrote in message news:elFZtoNQGHA.3672@TK2MSFTNGP14.phx.gbl... > Hi, > > I have a VB app that needs to export data via ADO to a pre-defined Excel > 2003 spreadsheet (has captions/headers to describe the data). The > spreadsheet has pre-defined cells for where the data goes. I also need it to > be flexible to allow the user to modify the excel spreadsheet (move data > around) without having to change my program. So, I cannot hard code these > cell locations in my code (ws.Range("J4").Value = rs!value1). I thought > about creating a template file that holds all the cell locations for the > data which would allow the user the ability to move data around. I also > looked into using pivot tables and XML however I do not like these solutions > for this app. Any other ideas? > > Thanks > >
Show quote
Hide quote
"will" <will@hasenoughspam.com> wrote in message This is likely a question better asked in an Excel newsgroup.news:elFZtoNQGHA.3672@TK2MSFTNGP14.phx.gbl... > Hi, > > I have a VB app that needs to export data via ADO to a pre-defined Excel > 2003 spreadsheet (has captions/headers to describe the data). The > spreadsheet has pre-defined cells for where the data goes. I also need it to > be flexible to allow the user to modify the excel spreadsheet (move data > around) without having to change my program. So, I cannot hard code these > cell locations in my code (ws.Range("J4").Value = rs!value1). I thought > about creating a template file that holds all the cell locations for the > data which would allow the user the ability to move data around. I also > looked into using pivot tables and XML however I do not like these solutions > for this app. Any other ideas? > > Thanks > While it would require a slightly more advanced Excel user, you might consider "Name Ranges". You could simply deliver your data to a block of named cells. The users could relocate cells anywhere and build their own elaborate layouts, but as long as both sides agreed on the 'names' neither would be trashing the work of the other. -ralph Hello, Will,
I don't know all of the constraints that you are working under, but I wonder if this wouldn't be better it you reversed roles. That is, instead of having VB "export" data to Excel, have Excel "import" the data from VB. You could then use your template to provide a number of custom functions that would return the required data. Users could put these functions into whatever cells they chose. Even in the case that VB must remain an exporter of data, you could pass the data to your template and use custom XL functions to access it from there. Cheers, Randy will wrote: Show quoteHide quote > Hi, > > I have a VB app that needs to export data via ADO to a pre-defined Excel > 2003 spreadsheet (has captions/headers to describe the data). The > spreadsheet has pre-defined cells for where the data goes. I also need it to > be flexible to allow the user to modify the excel spreadsheet (move data > around) without having to change my program. So, I cannot hard code these > cell locations in my code (ws.Range("J4").Value = rs!value1). I thought > about creating a template file that holds all the cell locations for the > data which would allow the user the ability to move data around. I also > looked into using pivot tables and XML however I do not like these solutions > for this app. Any other ideas? > > Thanks > >
Show quote
Hide quote
"R. MacDonald" <sci***@NO-SP-AMcips.ca> wrote in message An excellent idea! Reversing the view.news:440bed7d$0$24133$dbd45001@news.wanadoo.nl... > Hello, Will, > > I don't know all of the constraints that you are working under, but I > wonder if this wouldn't be better it you reversed roles. That is, > instead of having VB "export" data to Excel, have Excel "import" the > data from VB. You could then use your template to provide a number of > custom functions that would return the required data. Users could put > these functions into whatever cells they chose. > > Even in the case that VB must remain an exporter of data, you could pass > the data to your template and use custom XL functions to access it from > there. > > Cheers, > Randy > > > will wrote: > > Hi, > > > > I have a VB app that needs to export data via ADO to a pre-defined Excel > > 2003 spreadsheet (has captions/headers to describe the data). The > > spreadsheet has pre-defined cells for where the data goes. I also need it to > > be flexible to allow the user to modify the excel spreadsheet (move data > > around) without having to change my program. So, I cannot hard code these > > cell locations in my code (ws.Range("J4").Value = rs!value1). I thought > > about creating a template file that holds all the cell locations for the > > data which would allow the user the ability to move data around. I also > > looked into using pivot tables and XML however I do not like these solutions > > for this app. Any other ideas? > > > > Thanks > > > > VBA in Excel is as capable of utilizing ADO/DAO as VB is. One could also easily implement data fetches as ActiveX VB components and provide a startup sheet with a laundry list of available services. They could be invoked as you suggested as functions or as a Named Range of Cells depending on need. -ralph Thanks for all the posts, there are some great ideas. I should give more
background on this project. The Excel Spreadsheet is maintained by my client (Excel is their choice not mine). Like I said before this spreadsheet is a static sheet that predetermines where that data goes. I want to give flexibilty to the user by allowing them some functionality to allow the user to change the spreadsheet without changing my code. I want to keep all of my code in my app not in Excel. So, I think my initial idea of using a template file(data file or xml or...) might be the best option. Thanks Show quoteHide quote "R. MacDonald" <sci***@NO-SP-AMcips.ca> wrote in message news:440bed7d$0$24133$dbd45001@news.wanadoo.nl... > Hello, Will, > > I don't know all of the constraints that you are working under, but I > wonder if this wouldn't be better it you reversed roles. That is, instead > of having VB "export" data to Excel, have Excel "import" the data from VB. > You could then use your template to provide a number of custom functions > that would return the required data. Users could put these functions into > whatever cells they chose. > > Even in the case that VB must remain an exporter of data, you could pass > the data to your template and use custom XL functions to access it from > there. > > Cheers, > Randy > > > will wrote: >> Hi, >> >> I have a VB app that needs to export data via ADO to a pre-defined Excel >> 2003 spreadsheet (has captions/headers to describe the data). The >> spreadsheet has pre-defined cells for where the data goes. I also need it >> to be flexible to allow the user to modify the excel spreadsheet (move >> data around) without having to change my program. So, I cannot hard >> code these cell locations in my code (ws.Range("J4").Value = rs!value1). >> I thought about creating a template file that holds all the cell >> locations for the data which would allow the user the ability to move >> data around. I also looked into using pivot tables and XML however I do >> not like these solutions for this app. Any other ideas? >> >> Thanks |
|||||||||||||||||||||||
Other interesting topics