Excel Export

[Pwyd]

I was getting help from Ken Snell about exporting to excel, however, when i
tried to do it to a specific range, using the link he gave me, it wouldn't
work. In fact, the help files clearly state the following:

Note When you export to a spreadsheet, you must leave this argument blank.
If you enter a range, the export will fail.


How then does one find the next "blank" spot on a spreadsheet to export data
that isn't simply updated in the same spot every day, but is a log of whats
gone on over time.

 

[Klatuu]

That information in VBA Help is partially incorrect. You can specify a sheet
name, but not a range within a sheet.

Also, since about 2005, you can no longer append data to or update data in a
linked Excel spreadsheet. So, there are two ways you can accomplish this.

One would be to import the data into an Access table, append the new rows to
the Access table, then export the table back to Excel.

The other would be to use Automation to open the workbook and use the
CopyFromRecordset method to append the data, save the workbook, and quit the
instance of Excel.

 

[Pwyd]

Ah, yes, of course. Import the data from the spreadsheet into a table, make
changes, over-write the old one. That seems like a better choice than using
a second application to do something i can do with just the one.

Thanks Klatuu.

 

[Pwyd]

Klatuu i'm having one issue: when i try to export the excel spreadsheet back
to its place after modifying the data, i've tried using both
transferspreadsheet and the standard "export" command.

the Export command relies on the user to know what and where they are
putting, and what to name it. the Transferspreadsheet command on the other
hand isn't as malleable as i'd like, and with either one, it complains when
the spreadsheet already exists (i'm trying to make a destructive process, to
overrwrite the one that is already present, but it does not like this idea)

I don't really mind too much which one i have to use, though i'd prefer
"export" as its much easier to move the app. to another computer and not have
to change the command for each one.


Suggestions?