runtime error 3435 cannot delete spreadsheet cells

[oO P2K Oo]

Hi All,

Thanks for putting in time for viewing my post.

I am stuck with a runtime issue, the bit of code that seems to be causing an issue is the text below in bold

"DoCmd.SetWarnings True
    
  strFile = arwGetPersonalFolder & "\BromptonLungFunction.xls" ' & arwGetOfficeVersionExtension(OFFICE_EXCEL)
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblBLF", strFile, True
  varR = Shell("excel.exe " & strFile, vbMaximizedFocus)
  
  
End Sub

The "tblBLF" is within MSaccess, where the data is "sent" to (after carrying out the specific macro operations), which I have to export to excel once the code has completed.

It seems I cannot use debugging mode, to step into the code to find out what is causing the issue.
I have tried compact and repair database - with no luck
I have also tried removing the old data within "tblBLF" so it is blank, as after searching online some people have suggested that there may be formulas within the table. Again with no luck.
I have used the file previously with no issues.
I am running Access 2016 64 bit. I was running Access 2016 64 bit on a 32 bit machine, but since have IT upgrade my hardware. it was working fine before then, I am skeptical it is down to this issue alone as other Access functions work well for me.

Please could someone put me out of my misery? If you require any further info, or have questions please do ask.

Many thanks for your time.

 

[xenou]

Is this supposed to be an existing file, or a new file? Why are you using on old file format for excel? Have you made sure the path and file name are correct (after you concatenate them!) without any mistakes (arwGetPersonalFolder & "\BromptonLungFunction.xls")? Note that I only care about the file type because it's a little easier and more resilient if you go with the defaults.

Personally I have never used transferspreadsheet to try to edit an existing worksheet. I only use it to create/export to new workbooks. From the sound of the error description, you are doing something that deletes cells that you can't delete so possibly that can be taken at face value and it means this won't work for you in your situation.

Note: It is always possible that the use of 64-bit office can be an issue. It seems about time that Office 64 bit should really be the standard and 100% working as expected but in some cases you find that there are still some problems in some areas. I'm not sure what recent best advice is on this now. But as I said, I always found transferspreadsheet to be simple to use for export to new workbooks, and I don't really find it that great otherwise.

 

[oO P2K Oo]

Hi Xenou,

This is an existing file yes. I am using an old file format because this is what the creator of the file had done, unfortunately as I am not them and I am hesitant to play around with the code in case the whole thing ends up worse (I'm still very new to database coding and SQL).

I am not sure what you meant by "Have you made sure the path and file name are correct (after you concatenate them!) without any mistakes (arwGetPersonalFolder & "\BromptonLungFunction.xls")? when preparing to use this file, I select the file from an in tool prompt box, where I select the file I wish to work with and then press another button which initiates the query. Are you suggesting that I try changing the file name to BromptonLungFunction, and having the format as .xls?

Is there another way to possibly delete the cells, that aren't working with the code?

so do you think changing the .xls format to .xlsx would help me out?

Thanks again for your time and advice.

 

[xenou]

Well, although its only a few lines of code there is actually lot going on in these few lines of code.

this line:

strFile = arwGetPersonalFolder & "\BromptonLungFunction.xls" ' & arwGetOfficeVersionExtension(OFFICE_EXCEL)

represents a concatenation operation with a variable and a string literal.
So whatever is in the variable arwGetPersonFolder gets smashed together with "\BromptonLungFunction.xls". As a general rule, you need to be sure then that the results is really what you want and need.

For instance, if arwGetPersonalFolder holds the values "C\SomeFolder" then this would be an error (results is C\SomeFolder\\BromptonLungFunction.xls which has two slashes where there should be one).

If this has been working before and you are using a file picker then it's probably okay. Also there error is not consistent with filepath problems.

I don't know if changing to xlsx would help or not. Possibly. Although there is nothing wrong with xls files there has been a "new" file format for excel files for over ten years now. Perhaps its time to make the switch. With the old file format there is a need to make sure you are using the right file format option. And who really knows if acSpreadsheetTypeExcel9 is the right one or not? I don't. Maybe acSpreadsheetTypeExcel8 would work. Or acSpreadsheetTypeExcel10. These are answers you can only find out with some testing, probably. That's why I like using the defaults.

Can't really say much more - not my favorite function in Access as explained - not a function that gives you much control so I use it only for simple exports to new files.

If this is a new issue then you can try to think about what has changed? Has someone altered the file? Is it corrupt? Is it in use by someone who never closes it? Is it full? Is it empty? etc. etc.