Update Excel data from Access database

Question

How do I avoid losing Excel spreadsheet formatting when exporting Access data to existing Excel spreadsheet?

Answer 1

Formatting is applied to a range of cells, basically.

Depending on what you are doing here, there may be more than one approach that will work, but we really ought to have more details to help steer in a useful direction.

Is there a consistent range of formatted cells in this spreadsheet? By that I mean is the same number of rows and columns exported each time? Are there predefined areas of Column headers? Summary rows?

And, of course, the basic question is what formatting is needed? Highlighting, colors? What?

One method could be writing VBA to apply the required formatting after the export. I've seen that done, but it can be laborious and slow. The result was brilliant, though. The developer who did that is one of my heroes.

Another method could be to create TWO worksheets.

One is a "landing page" for the raw data. This would be where Access exports the data, overwriting or replacing what was there previously. The other is the pre-formatted "display page" which has links to the raw data. This works well with highly consistent data outputs, but not as well if the amount of data is different from export to export.

Give us the details, someone can then offer specific suggestions.

Thank you.

Answer 2

Hello,

Greetings for the day! I’m an Independent Advisor and Microsoft user like you. Thanks for posting the query here at this forum.

Could you please try creating a Named Range in excel before loading the data from Access database?

To create a range, https://support.microsoft.com/office/fd8905ed-1130-4cca-9bb0-ad02b7e594fd

1. Select the range you want to name, including the row or column labels.
2. Click Formulas > Create from Selection.
3. In the Create Names from Selection dialog box, select the checkbox (es) depending on the location of your row/column header. If you have only a header row at the top of the table, then just select Top row. Suppose you have a top row and left column header, then select Top row and Left column options, and so on.
4. Click OK.

Make sure the reference, for example: =Sheet1!$A$1; refers to the range where you want your data to be pasted in the existing excel. Try not to have the Excel file open while you run the subroutine. Then when you are exporting from Access you can use a VBA module to run the following:

Private Sub ExportMyQueryOtTableToExcel() DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _ "sqYourQueryNameOrTable", "x:\ExcelFile.xls", -1, "NameRangeInExcelWorkBook" End Sub

https://stackoverflow.com/questions/25641752/exporting-data-from-access-to-an-already-existing-excel-spreadsheet Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.

Hope this information helps. Please feel free to get back if you have any questions.

Thank you! Ravikumar Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

Answer 3

Thanks. As I said, my skills are limited. I use the Access Export to Excel in a Saved Export. The fields are identical every time. I just want the rows to update with the new data. Sometimes there may be fewer rows, other times maybe more rows.

The formatting is simple: column width, wrapping text, column headers repeating on each page. Reformatting the spreadsheet every time I do an export is not an option.

Hope this is enough info. Sorry if it is not.

Thanks again.

Answer 4

No, there is no "setting" to do that. I offered two approaches. One might be more effective than the other, depending on the details.

What you are after is not "out of the box" functionality, and that means "fixing" it isn't a matter of finding a setting.

Explain, please, what the data is.

I previously outlined the parameters that might work for a highly consistent output. Is that what you have? Or are there different numbers of rows and columns each time the export occurs? Start with that.

Also, it could be highly helpful to someone trying to assist to see the code you use for this export. Or are you just using the basic "Export to Excel" from the ribbon? Again, the more you share, the more likely it is someone can help.

Answer 5

Thanks for the reply.

Simply put, my problem is my Access export to the spreadsheet creates a new, unformatted sheet instead of overwriting the data in the existing, formatted sheet.

My skills are limited, so I was hoping there is a setting somewhere to fix this.