Exporting a Row at a Time (1 Viewer)

[alexfwalker81]

Can Access write a new row into an Excel spreadsheet, or does it have to overwrite the entire sheet each time?

 

[theDBguy]

You can tell Access where to put the records (which row to start) by using Excel Automation.

 

[alexfwalker81]

You can tell Access where to put the records (which row to start) by using Excel Automation.

So, if I had a spreadsheet which was potentially in use in between Access writing to it, I suppose I could import it (or part of it) to get the number of rows in the sheet, then use this info to tell Access where to write next.

 

[June7]

Don't need to import sheet to find number of rows. Excel automation code can find first available row and start writing data at that location. Multiple ways to do that. Google or Bing the topic.

 

[theDBguy]

So, if I had a spreadsheet which was potentially in use in between Access writing to it, I suppose I could import it (or part of it) to get the number of rows in the sheet, then use this info to tell Access where to write next.

You can also use code to find out which row is empty or find out which one is the last row.

Edit: Oops, @June7 beat me to it. Sorry...

 

[Isaac]

Just adding to what June and dbGuy have said ... Another reason it might be important to pick one (import, to check last row) vs. the other (use Excel automation, to check last row) is because the two methods might actually give you different results..Depending on how you want to define the last row, and depending on whether the import method would actually import a lower row due to saved 'formatting' existing on a lower-but-blank row. And, the reverse can also happen. The most common technique (in Excel automation) that people use to determine the "last row" sometimes throws an unexpected result, also, due to saved 'formatting' (or the official size of a Table, despite containing blank rows).

For more information on that & my recommendation on how to protect against it, if using Excel automation, see this.

 

[alexfwalker81]

Ok, great - thanks all, that gives me plenty to work with!