Exporting to the same excel sheet

[MarionD]

Hi there all,
After spending the whole morning searching the archives and experimenting I still cannot do what I need to, so I decided to start a new thread in the hope that someone out there will take pity on me!

I have an excel sheet with 2 columns and x rows. There is a diagram on the sheet that reflects the figures in the 2 columns. I need to export the data from a query, to the same sheet - overwriting the existing columns and rows. i.e. delete the data from column 1 and 2 and refill the columns with the data from my query so as not to lose the diagramm.

I have only managed to create a new sheet each time (without the diagramm). The GetObject(path,class) doesn't work for me.... (I'm assuming that getObject gets an existing object rather than createObject)

Would really appreciate any help
Thanks
Marion

 

[BCullenward]

In excel, you can import the data from an external source.

just choose Data>Import External Database Source> New Database Query

then pick your database from that and what query you need. You can then refresh the data by right-clicking on a cell and picking Refresh Data... or you can make a button that will refresh it for you.

You may need to recreate your chart though, as that will allow it to dynamically fill the chart with your data.

 

[MarionD]

Hi there and thanks for the reply.
The problem is that I don't want the user to have to open Excel and import the data - I would like to be able to export the data to Excel with a button the the form. I've created an "original" Excel sheet, then copy this sheet and export the data to the new sheet... the updating of the diagram is still a problem though.
Thanks again for the response
Marion

 

[modest]

This has been covered on this site already. You will have to open and SELECT a new sheet to post data to.

 

[MarionD]

Thanks again. Sure it's been covered on this site already - but still I can't find the solution. I don't want to create a new sheet each time - I want to open the same sheet and overwrite the first 2 columns and n number of rows.. the number of rows could vary. If for example, the first time the sheet is populated there were 20 records (20 rows), the next time only 18, then the last 2 rows must be cleared. I need to update the diagram as well, so that it reflects the data in the first 2 columns.
Thanks anyway, sorry if I'm trying to reinvent the wheel!

Marion

 

[Dwight]

MarioinD:

I have also researched and attempted what you are trying - and failed. I think what Modest is saying is that so have many others.

I don't think it is possible for Access to export data to a specific range within Excel. Even the TransferSpreadsheet (the most obvious method to use) makes it clear that you cannot export to a set range.

I recommend you focus on creating the best solution for your users given this inconvenience.

 

[MarionD]

Thanks Dwight - I don't want to give up completely yet - will let you know if I find a way to do this

Marion

 

[Jack Skeleton]

Hi Marion,
I've practiced a little bit sending Access recordsets to Excel, and I think what you want to do is possible. You mentioned that the "Getobject" method doesn't works... as you mention, it works with an already created object. If you have an original Excel file ("master"), you can open it, modify the values in the rows you want and save it under another file name. I've just written an example of the Getoption action in another thread, I think it can suit you.
I'm in the point of going on holidays, so I cannot explain too much now. But a tip I hope you'll find helpful:
Excel has a wonderful "automatic Creator of macros". You can grab what you do "manually" in Excel in a macro, and after that copy the code created adapting it to the be managed under an Access module (there are only little differences).
OK, only some example code:

dim Exceltmp as object, myrows as integer, mycolumns as integer

Set Exceltmp = GetObject("here the complete path and file name, including the .XlS extension", "Excel.Sheet")

for myrows = "firstrow" to "lastrow"
for mycolumns =  "firstcolumn" to "lastcolumn"
Exceltmp.cells(myrows, mycolumns).formular1c1 = "valueyouwanttoputin this cell"

[COLOR=SeaGreen]'to fill the values in every cell you can create a recordset with the content
'of your Query and move record by record using the "for...next" loop.
'...I'm sorry, I don't have time enough to write the whole code now, but
'I hope it will help you to find the solution[/COLOR]

next mycolumns
next myrows
 Exceltmp.Application.workbooks(file name without path but including ".XLS").saveas filename:="here the whole path and new file name", CreateBackup:=False
Exceltmp.Application.workbooks(new file name).Close savechanges:=True

 

[MarionD]

Thanks Jack,

Have a nice holiday!

Will test and let you know how it works out!

Marion