TransferSpreadsheet

[Guest]

Hi everyone,

I'm using docmd.TransferSpreadsheet to export a table into an existing Excel
spreadsheet. I want it to replace all the data saved in one particular
worksheet which I have done but it leaves loads of blank rows between the
first row which holds the field names and the actual data. I think the blank
rows are left there because that is where the old data was.

Does anyone know how to prevent this from happening. If not can anyone tell
me how to delete the empty rows using VBA code written in Access.

Thanks!

 

[Guest]

Without seeing the code, I'm assuming that it does some type of append to the
worksheet. The code needs to specify which cell to start the copy at -

XlSheet2.Range("rngListboxComments").ClearContents 'delete
existing data
XlSheet2.Range("a2").CopyFromRecordset NameOfRecordset 'add new data

If this is not true, please past a copy of the code to help troubleshoot.

 

[Guest]

Hiya,

Thanks for your reply.

The code that I used was:

docmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Data",
"C:\Data\spreadsheet.xls", True, "Data!"

You were right though, I do have to specify which cell to start he copy at.
So I tried it and it works! I missed out the cell range after specifying the
sheet name. My code now looks like this:

docmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Data",
"C:\Data\spreadsgeet.xls", True, "Data!A:I"

 

[Guest]

Hi again,

I've come across another problem. The TransferSpreadsheet code works fine
when I want to replace data in a specific sheet, but it doesn't work when I
want to replace data that is referenced in tables using Vlookup to pull out
some of the data. It comes up with a run-time error saying that the
spreadsheet cells holding the old data cannot be deleted.

Is there any way to make this work or get round this problem?

Gina

 

[Guest]

Without seeing the code, I'm not sure from your description what the code is
doing. Is it trying to update (overwrite) specific records, is it trying to
update specific fields, it is trying delete specific records based on
criteria and add new ones, is the worksheet password-protected?

In general, I highly recommend a Wrox book - Excel 2000 VBA: Programmers
Reference (there's a 2003 version if that's what you're using). Excel VBA
functions can be run within Access VBA and it may be that that would be the
easier route.

 

[Guest]

The code that I am using is:

docmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Data",
"C:\Data\spreadsgeet.xls", True, "Data!A:I"

This code is being run within Access VBA already. From my understanding of
the code it is exporting the data from my access table into the worksheet
called 'Data' which is within the workbook called 'spreadsheet.xls'. It is
trying to delete the exisitng data and then pasitng in the new Access table
data into columns A to I.

But i reckon it can't delete the existing data because specific fields for
each record are being used in vlookup formulas elsewhere within the same
workbook.

In answer to your questions the code is not trying to delete specific fields
based on criteria, it is deleting all data on the 'Data' sheet before pasting
the new data. The workbook/worksheet is not password-proteted either.

Do you or anyone else know how I can make this code work so it can delete my
existing data and replace it with the new data, whilst keeping my vlookup
formulas in other sheets within the same workbook???!!!