Mik Mak
asked on
Excel repeating rows disappears after data-insert
I have an excel file (xlsx) where I want the to`p row to repeat at the top of each printed page - normally thats not a big issue, but on this the data is exported from Access into this excel file, and the appears to clear that particular setting. I have tried to put the Access query result in a separate sheet, but with the same result. It looks like the area defined gets cleared when one of the sheets gets updated.
I could just do this in code in the excel file, on opening, but would prefer not to have an xlsm file. Any ideas on how to circumvent this annoying "feature" :) ?
I could just do this in code in the excel file, on opening, but would prefer not to have an xlsm file. Any ideas on how to circumvent this annoying "feature" :) ?
ASKER
Hi John, thank you for your input - but I'm just asking about how to make sure that excel repeats the top row on each printed page - the rest is not a problem :)
perhaps you could create an Excel report template, and reuse the data that has been exported and pump into this Excel report template.
additional task is needed but you will no need to reformat the output every time.
Current process (As-Is):
Access > Excel
Expected process (To-Be):
Access > Excel > copy data to Report Template > Save as output
additional task is needed but you will no need to reformat the output every time.
Current process (As-Is):
Access > Excel
Expected process (To-Be):
Access > Excel > copy data to Report Template > Save as output
ASKER
I am using an excel report template :) - and everything is fine (row widths, colors etc) - - except for the "rows to repeat at top" which gets cleared/disabled everytime I transfer new info/records
ASKER
I'll probably have to do it in code - although I was hoping for a solution that would work in a xlsx :)
Just one question
The access query that throw the data onto your excel does it:
1) ADD data to an EXISTING sheet in excel ?
2) Create a new sheet in Excel with the data from the Query ?
3) Reset an existing sheet in Excel and puts in the new data ?
Which of the 3 options you have ?
Gowflow
The access query that throw the data onto your excel does it:
1) ADD data to an EXISTING sheet in excel ?
2) Create a new sheet in Excel with the data from the Query ?
3) Reset an existing sheet in Excel and puts in the new data ?
Which of the 3 options you have ?
Gowflow
ASKER
I have a template file, and the query is exported into that - and the excel file then has a "front" sheet that takes the data from the underlying sheet - don't know if that counts as 1 or 3 in your list :)
Did you write the query or it is a query that u downloaded ? coz it all there.
Let me explain, The query has a choice (or you can set it to CREATE a sheet in the destination excel file and throw the data OR Delete all data from existing sheet then throwing the new data inn that existing sheet or Adding data to an existing sheet.
The trick (I guess) for the sheet not to loose its setting ie repeat row 1 at every page is not to have a NEW sheet created.
So you need to look into how the query is built if you have the syntax just post here I will have a look at it.
Gowflow
Let me explain, The query has a choice (or you can set it to CREATE a sheet in the destination excel file and throw the data OR Delete all data from existing sheet then throwing the new data inn that existing sheet or Adding data to an existing sheet.
The trick (I guess) for the sheet not to loose its setting ie repeat row 1 at every page is not to have a NEW sheet created.
So you need to look into how the query is built if you have the syntax just post here I will have a look at it.
Gowflow
ASKER
Its a query run in Access - as I mentioned in the question the data is exported from Access into this excel file :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Mik Mak I know it is in Access and Access is not a mystery box !! someone wrote this query you need to display the query and post it here.
Gowflow
Gowflow
ASKER
You're right - I've been working with Access for 25 years :) I wrote the query, but who wrote it and the actual content are irrelevant - its just aboring old select query that gets transferred to excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xm
- bottom line is that doing this "top rows to repeat at top" is reset when doing the export into the excel file, while every other formatting done beforehand is kept in place. I was hoping to find a solution that didn't involve code in the excel file itself - but it doesn't look like it is possible - and I'm running out of time - so I just made the small format ajustment in directly in the excel file
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xm
- bottom line is that doing this "top rows to repeat at top" is reset when doing the export into the excel file, while every other formatting done beforehand is kept in place. I was hoping to find a solution that didn't involve code in the excel file itself - but it doesn't look like it is possible - and I'm running out of time - so I just made the small format ajustment in directly in the excel file
If Excel is necessity then maybe you could count the rows of Excel, use the Access query to create a temp table and use Access to insert (inject) records at predefined intervals
So for example each Excel page has 30 rows...so you take your Access query create a temp table ,use the Recodset objet to read the table to insert records at predefined positions.
I wouldn't go that way personally and instead would use Excel Automation , treat Excel as object , iterate the records of the query , calculate each time the rows , have the "header" as some static content and insert it accordingly.