Kill Excel spreadsheet but keep formating

[Danick]

I'm using VBA to export an Access Query to an Excel Spreadsheet

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, WbSheet, CurrentProject.Path & "\" & WbName, True

But before exporting, I need to clear the contents of the spreadsheet as it may contain data that is outside the named range. If I don't delete all the rows, then I sometimes get an error 3434 - "Cannot Expand Named Range"

    strFile = CurrentProject.Path & "\NameOfSpreesheet.xls"
    If Len(Dir(strFile)) > 0 Then
    Kill strFile
    End If

This is working fine, except that it also gets rid of all the formatting such as column width, shading, borders, etc...

How can I transfer the data to Excel without disturbing the spreedsheet formats.

 

[isladogs]

Create an Excel template file with all the formatting but no data.
Populate the file then save as a different file name.

 

[Danick]

Create an Excel template file with all the formatting but no data.
Populate the file then save as a different file name.

OK - So you mean I should use the DoCmd.TransferSpreadsheet acExport to the template instead of the .xls file?

I tried that but am getting a Run-time error 3274 "External table is not in the expected format"

Any other suggestions?

 

[ashleedawg]

[Edit: Woah, that was a HUGE font... It's my first reply here; still kinda gettin' used to the place,lol] Access gets finicky when exporting to the same Excel sheet more than once, especially if someone's messed with it since the last time. There are more reliable ways of getting data into Excel, but TransferSpreadsheet is the simplest.

This is what I've done in the past:

1. Let Access create a new XLS file, specifying the name of the worksheet:

Const sourceData = "tblYourSourceData" 'source data (name of your Access table or query)
Const wbName = "ExcelFile.xls" 'use a filename that doesn't exist yet; let Access create the workbook
Const destTab = "xlDataTab" 'something for Access to name the new worksheet it creates
DoCmd.TransferSpreadsheet  acExport, acSpreadsheetTypeExcel9, sourceData, CurrentProject.Path  & "\" & wbName, True, destTab, False

2. Open the new file in Excel and don't do anything to the new Data tab beyond maybe changing the column widths.

3. Manually add another tab to the workbook, and add formulas referencing the data tab. For example, if you need a few rows at the top for titles, etc, go to cell A5 on the new tab and use formula =xlDataTab!A1 and copy/fill until you have all the data you need. Do whatever you want to the new tab (formatting, inserting, moving around, formulas), just don't change that data tab directly! When you're done with it, hide the data tab so it doesn't get accidentally touched.

From then on you should be able to run the same export from Access into the existing spreadsheet any time you want, the other tab(s) will always be using the most recent import (and Access will let you get away with it as long as the structure of the source data doesn't change).

At one point I had dozens of workbooks with several sheets each, imported like that daily. It "worked" but the limitations of TransferSpreadsheet eventually motivated me to learn more VBA...

 

[Danick]

Thanks ashleedawg for that detailed explanation, but unfortunately that won't work for me.

I am trying to export data from an Access Table to an Excel Spreadsheet so that I can send that spreadsheet to clients who would then edit or append data to that spreadsheet and then send it back to me.

When I get it back, I save it using the same name as the original spreadsheet that was exported. After some error checking, I Import that spreadsheet back into the table using a acimport and an update query.

So this is why I need to delete the spreadsheet before starting the process. The only issue I have with this is that deleting the worksheet also deletes everything. Even macros!!

I suppose I could try to just export it to a temp file and then use a macro in a different file to import that data, but I thought there should be something a little more automated...

 

[isladogs]

Hi

Your original code shows

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9

This is fine if you are still using Excel 2000 .xls format
If however, you are using the newer .xlsx format, the code should be:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml

So you mean I should use the DoCmd.TransferSpreadsheet acExport to the template instead of the .xls file?

Yes I did mean that

I tried that but am getting a Run-time error 3274 "External table is not in the expected format"

This means the file you are exporting to does not match the format for the transfer spreadsheet process

Your 'template file' needs to be the same file format as your final spreadsheet i.e. an .xls or .xlsx file

 

[ashleedawg]

This clears the contents from a range of cells (or a whole worksheet) and leaves formatting intact:

Sub ClearValuesFromWorksheet()
    Dim oXL As Object, wb As Object, ws As Object
    Set oXL = CreateObject("Excel.Application")
    Set wb = oXL.Workbooks.Open("yourfilename.xls")
    Set ws = wb.Sheets("worksheet-tab-name")
    
    '[B]Use either[/B]; [I]clear values (not formats)[/I] from a range:
    ws.Range("A3:C15").Clearcontents
    '[B]OR[/B], [I]clear values (not formats)[/I] from the whole sheet:
    ws.Cells.Clearcontents

    wb.Save
    wb.Close
    Set wb = Nothing
    Set oxl = Nothing
End Sub

I seem to recall being stuck on that issue for a while before I realized the subtle difference between:

• .clearcontents (clears values only)
• .clear (clears everything)

Is this more what you had in mind?

 

[ashleedawg]

This means the file you are exporting to does not match the format for the transfer spreadsheet process
Your 'template file' needs to be the same file format as your final spreadsheet i.e. an .xls or .xlsx file

Some older versions will throw that error as an (annoying) general "something went wrong", like if the Excel file isn't closed properly when you run the TransferSpreadsheet, or an improper OLEDB installation, or silly connection string problems, and it was a known issue with TransferSpreadsheet that wasn't fixed until later versions.

I'm starting to recall the headaches I had with that in the past. I think that methods isn't really intended for any ongoing back-and-forth transfers. (I would post a link [to codeproject question 537510 or to Excel2007 on connectionstrings dot com] but I'm not allowed to just yet.)

(And honest, I'm not trying to one-up the guy who inspired me to join this forum, only a few hours ago!)

 

[isladogs]

The approach I'm advocating has worked perfectly well in all versions of Access from 97 onwards.
By using a template you don't see to worry about 'clearing data' as the populated version is saved with a different name.
So the 'template can be used tens of thousands of times without problems

Attached is a 'typical' template in xls format.
In this case, data is added to the Grades worksheet.

This one is unusual as it has an explanatory worksheet as well
Most only have one worksheet

P.S Feel free to give a better answer than me... lots of people here do so!

 

[Danick]

Thanks for all the suggestions.
I tried the clearcontents and it seems to work. But for some reason, it crashes my Excel if I do it a few times consecutively. Then I have to use the task manager to stop Excel and then it works again. Can't figure out why it hangs sometimes.

So instead of trying to figure out why it's crashing, I went a different route.
Basically, I am now killing a temp table and acexport the data to that table. No more problems with Excel.

For the formatting, I created another spreadsheet and linked the two spreadsheets. It's an extra step to Refresh the connection, but so far it seems to be the best compromise in simplifying the process and getting a nice formatted Spreadsheet to send to clients.

Thanks again for all the suggestions.

 

[Danick]

The approach I'm advocating has worked perfectly well in all versions of Access from 97 onwards.
By using a template you don't see to worry about 'clearing data' as the populated version is saved with a different name.

Hi Ridders
I only saw your response after I sent mine.
But after looking at what this is doing, it seems to be almost exactly what I'm doing now. But instead of saving the template to a new name, I've just linked the template to a formatted spreadsheet.
Thanks again!!

 

[isladogs]

Hi Ridders
I only saw your response after I sent mine.
But after looking at what this is doing, it seems to be almost exactly what I'm doing now. But instead of saving the template to a new name, I've just linked the template to a formatted spreadsheet.
Thanks again!!

Not quite sure I understand that.
The point of using a template is that is reused endlessly
However if you've got it working then that's fine by me!

 

[ashleedawg]

Oh oh, I might be the reason for your crashing earlier - You should probably at this to the very end of that sub:

Set oXL = nothing

You might wanna hit your CTRL+ALT+DEL and kill all the extra copies of Excel running in the background... Sorry about that!

I was just about to say that it's no problem; it's kinda nice getting back into coding & DB's after a few year "apart", and this is the first forum I ever joined.
Hmm still a little rusty I guess!

 

[isladogs]

@ashleedawg
Well that certainly would explain the crashes experienced by danick!

And I'm really not trying to do one-upmanship (?) either, but I really would suggest that you both try the method I proposed.

Its simple & it works with no memory overhead or error messages (in my experience)

 

[Danick]

Not quite sure I understand that.
The point of using a template is that is reused endlessly
However if you've got it working then that's fine by me!

But the file you provided is an .xls file. I'm using Excel 2013, so for me a template gets saved as an .xltx file. This is probably why my Access 2003 isn't liking that file format.

So to get this to work, I'm using two files. One called Update.xls and another called UpdateTEMP.xls. The UpdateTemp IS my "Template" file and gets reused. The DoCmd.TransferSpreadsheet acExport method transfers the data in the back ground to the UpdateTemp file. But I need a clean file before the transfer, so I delete the file first and then export the new data into the UpdateTEMP.xls.

Right after that acExport, I use VBA to open the Update.xls file and press the "Refresh All" button. This will pull in the data from the UpdateTemp but still remain it it's proper format. Actually, I kind of like the look more when using Connections as it does a line by line double shading in Blue and light Blue automatically.

ashleedawg, don't worry about the
Set oXL = nothing
I did do just that with the CTRL+ALT+DEL and killed all the extra copies of Excel running in the background. No problem.
Thanks for showing a way to clearing the contents rather than just using Clear. I may go back to using that method instead if this connections method becomes a problem in the future.

Thank you all again for your time and suggestions. It is very much appreciated.

 

[isladogs]

But the file you provided is an .xls file. I'm using Excel 2013, so for me a template gets saved as an .xltx file. This is probably why my Access 2003 isn't liking that file format.

Back in post #6, I wrote:

Your 'template file' needs to be the same file format as your final spreadsheet i.e. an .xls or .xlsx file

I put it in quote marks to indicate you should use an xls or xlsx file AS a template - NOT create an .xlt / xltx file

Also in the same post, I wrote

If however, you are using the newer .xlsx format, the code should be:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml

If you do both of those things, it WILL work