Need to export data to existing worksheet, not workbook

Question

I have setup a system to export a report from our reservation system to an Excel workbook so I can then do some calculations on the data and format a new report.&nbsp; &nbsp; The thing is that I have to then create 2 separate workbooks, one for the export of the data, and then one that then takes that data from that workbook and imports it into another workbook in order to be able to do the calculations and formatting.&nbsp; &nbsp;I deally I would like to be able to export the data from our reservations system to one specific worksheet within an existing workbook, instead of having to use 2 separate workbooks and overwrite the file every time I need to update the report.&nbsp; &nbsp; Is this possible?&nbsp; &nbsp;I do not have any access to the export command being used by the reservation system, so not sure if that will be an issue.&nbsp; &nbsp;

charla74 · Answer

lamplighterInn&nbsp;&nbsp;It would be possible to create a VBA routine which would move the exported data sheet to the destination file where you will work on it.&nbsp; You would need to create the code in your PERSONAL.XLSB (which means it is resident in Excel itself, rather than a specific workbook) which you could run once you have the exported workbook.

sergeibaklan · Answer

lamplighterInn&nbsp;
Perhaps Power Query could help to take data from sheet/book or directly from reservation system and put it into another sheet, transform as necessary.

lamplighterinn · Answer

Charla74&nbsp;&nbsp;The whole idea is to not have to create the second workbook to begin with.&nbsp; &nbsp;I already have routines to copy the data from the exported workbook to my calculation workbook.&nbsp; &nbsp;Is there a way to change the "Save as" function to say a specific workbook and worksheet, vs just the name of a workbook?&nbsp; &nbsp; Right now my save as command says save my data to:&nbsp; ".../desktop/dailyreport.xls"&nbsp; &nbsp;Which completely overwrites this file every time.&nbsp; &nbsp; &nbsp;Is there a way to change this to say&nbsp; ".../desktop/dailyreport:worksheet1.xls"&nbsp; ??&nbsp; &nbsp; and NOT have it completely overwrite the entire file, just the worksheet specified?&nbsp;&nbsp;

charla74 · Answer

lamplighterInnThis then becomes a question about your reservation software rather than Excel, which I am not able to answer with any authority, sorry. At a guess, I would say this would not be possible as you're essentially asking your reservation system to open / edit an existing workbook, and more to the point, it should know exactly what do in there.

Forum Discussion

Need to export data to existing worksheet, not workbook

4 Replies

Resources