[Solved with workaround] Spreadsheet as New Source

[jw_horton]

Biggest issue is that I don't use mail merge enough. First time in eight months. New document, new spreadsheet to feed merge. Followed your wiki documents to create new DB, Spreadsheet as connection, specified both .xls and .ods versions of spreadsheet with different attempts. Same result, connection was successful but never was a sheet list displayed to specify data connection. Going back to Data Sources in Writer, no tables show to select. Either spreadsheet has only one sheet so no overload. Tried this at least six times with either source. Even found an old document that specified saving the spreadsheet in XML format (which crashed Base when "Finishing" the connection.

At first, I thought that I had a problem as I'd opened my last successful merged doc to see what's what and when working with the new document, the old, registered DB was preventing me from opening the new source. But it was still the same issue that I generated this first time (no sheets/tables to see).

Understand that this is supposed to create a DB "stub" that houses the connection as when I tried to open the .odb file from my last successful merge, it opened in Writer with just a few odd characters.

Was under a time crunch and had to pass off to a colleague who still has Word and had to admit that I couldn't get this done in OpenOffice.......... BUT need to get this successful and better documented as I'll need this in a few more months.

[Villeroy]

Edit the spreadsheet (xls or ods does not matter in this case)
Save the spreadsheet
Shut down the whole office suite
Start again
You should see the saved changes now.
-----------------------------------------------------------
A calculator is the worst type of data source.
Create a dedicated directory
Save the sheet as dBase (*.dbf)
Open the database document which is connected to the spreadsheet.
menu:Edit>Database>Connection Type
Type: dBase
Location: the dedicated directory
Now you have a simpole database that updates automatically

[jw_horton]

I'll assume that by "edit the ss" you meant to define a new DB using the SS as a data source?

Had pretty much followed these steps in prior attempts. However, walked through them as specified. No difference, no tables. A spreadsheet is the only way my application can export data for such a merge. So even if "worst", it's the most common (especially in the "Word" world) and is the only source for my purposes. A few years ago, I didn't have to go through this new/database route; I could just specify as new/spreadsheet when adding database from Writer. It changed this last year and now I can't get it to work at all. I even tried to break down and import the sheet into a .odf but could find no import function.

[Villeroy]

Edit the spreadsheet means write your data into the grid. All I can tell is that this works just fine for me except that any changes in the spreadsheets do not transfer to the serial letter without restarting the office suite

[jw_horton]

Had already read a few of those types of problems which seem related to the data latency that you've indicated.

My problem is the SS exists (with data, of course) and am trying to make the connection. Hitting the "test connection" is always successful when setting it up but no tables/sheets show up that have the data...........

[Villeroy]

The data table resides on Sheet1 with one row of column labels (Forename, Surname, ZIP, City, Address etc) and data below the labels.
Spreadsheet is saved as C:\Path\MyList.ods
Database document is C:\Anywhere\MyDB.odb
C:\Anywhere\MyDB.odb is registered under the name "MyDB" (see Tools>Options>Base>Databases)
When you open C:\Anywhere\MyDB.odb the status bar reads: "Spreadsheet" and "C:\Path\MyList.ods"
The tables container has a table "Sheet1" with the columns that are labeled after the first row.
The serial letter, which must be a Writer document (*.odt), contains database fields pointing to
DB_Name.Table_Name.Column_Name
for instance
MyDB.Sheet1.Forename
Hit Ctrl+F9 if you don't see the full reference.

Writer's database fields pull data from database documents. The database document can be connected to anything tabluar:
[Anything with tables and columns] ---> [Database Document] ---> Writer:[DB.Table.Column]

[Villeroy]

In Base you can edit the database document if the spreadsheet pathname is wrong.

On the options dialog you can edit the database registration if the database pathname is wrong.

In Writer you can use menu:Edit>Exchange Database... if the registered db-name and/or the table name is wrong.
If the column name(s) changed, you can either switch to a query with column aliases or you adjust them in the spreadsheet or you exchange the database fields.

[jw_horton]

Vileroy,
Sorry you spent so much time on your response and color selection. It describes all that exists WHEN THE DB CONNECTS PROPERLY. And had happened the several times, in the past, that I had done this successfully.

I establish the connection, per the documented instructions, the connection is successful, the new DB is registered.
And there are no tables to display in this new database....................
I select the Data Sources, see the new DB, Tables and Queries are "ghosted", they do not contain anything. Wasn't expecting Queries but definitely was expecting to see Tables display Sheet1.

At least 10 times, with several variations, the result is consistent. So much so that I had to vary the DB name to not be confused by the numerous, and "no table" .odb files that were piling up in my directory.

I've performed mail merges for decades with Word but have never experienced such frustration when I had to switch to OpenOffice for financial (and moral) reasons. What was, at first, relatively the same as Word, it's degenerated into hooking the spreadsheet into Base and, now, it's not at all.

[Villeroy]

Unzip the attachment, register the odb as "MyDB" and see if that one works.

[jw_horton]

Sorry,
Not accepting zip files from strangers. Even if it does work, it would not prove anything. If you're walking towards an "improper spreadsheet" or something, that's too far off base. The last odb that was created a few months ago still registers properly, with it's accompanying .xls and functions. The .xls that was generated this time was in the same format as it and several prior successful files were.

[Villeroy]

If you ever find out what the problem is, don't forget to post it here.

[jw_horton]

36 years in IT does create determined professionals. Plus, I had an extra hour to get back to this, finally. Had hoped for a forum solution but I had to forge one of my own.

Somewhat of a bug has been discovered. As I said before, these spreadsheets have been connecting fine, until just now and successfully this last March. Started with a virgin SS with just two column headings. Connection was just fine. Headings showed up but, of course, no data. New SS had the default 3 sheets. I always default mine to 1 as the extras are hardly needed and I'll add sheets if I need some. Added, successively and created serially new DB's to connect, a second and then third additional sheet to no success.

This was to be expected as my starting test only displayed the Sheet1 with any data (columns only) and ignored the other two empty ones. This gave me the clue. My exporting application always defaults (with no way to control) narrow buffer columns between the real data columns, including the lead data column, A. Therefore my resultant SS has a lead column that's blank; no column heading and, of course, no data. Deleted the lead, blank data column and the connection was successful.

Bug to report: Base (with Spreadsheet connection) now seems to query the A1 cell in a sheet to determine if a connection is to happen. If all sheets have no A1 with data, all sheets are ignored. If only one sheet has a populated A1, only that sheet is "connected". Someone must have coded that "fix", relatively recently, to avoid connecting to blank sheets. Oddly, the successive, blank columns beyond the now-populated A column showed up in data sources as their respective column names (i.e E, G, H, etc.). Which I regard as an additional bug as they've never appeared before. Apparently, the earlier connection check scanned all columns in row 1 for field names to construct it's view but now defaults column designations with no row 1 name as a valid field name?

[Villeroy]

Thank you for posting something reproducible.

Workaround for a database-like list on a sheet which is not the area of used cells around A1. This is also a work-around for sheets containing more than a database-like list. A spreadsheet has no concept of tables, fields and records. It is just a grid of empty cells that can be filled arbitrarily.
-------------------------------------------------------------
Select a database-like list including a header row.
menu:Data>Define...
Enter some range name, say "Data"
Save the spreadsheet.
Restart the office suite.
Now you have a table named "Data" that appears in the Base document and in the data source window.