update an Excel workbook

So I have an Excel workbook with three Worksheets that is basically a weekly executive summary of a lot of data in a postgresql database -  

The main Worksheet will eventually have 12 data columns and I will use the Month as the offset. . While worksheet2 is also updated and the content in Worksheet 3 is replaced.

The source of the data is currently a series of SQL selects that all output to a text file, and then someone, namely me, has to update the Workbook and send the updated result to an Exchange group for distribution.  Obviously the goal is to automate this and run it out of Cron weekly.

I moved the xlsx to my FreeBSD server and using unzip pulled apart the xlsx.  But there are a lot of components and it' s not obvious how for example the "sharedStrings.xml" is updated in relation to the worksheets/sheet1.xml files.

Hoping someone may have a link to a whitepaper or book that would give me better insight into the relationships here, and on how to work with these files before I zip them back up and distribute them.  

TIA --
LVL 15
PhonebuffAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
Please post a representative sample of the source and destination files.  Do not substitute XML files for XLSX files.
0
Michael FowlerSolutions ConsultantCommented:
Without more detail it is hard to say but one option here could be to do the work at the SQL side using Views and/or additional tables.

This would enable the excel to be live and users would only need to open/update the sheet to see the latest data.

https://support.office.com/en-gb/article/Connect-a-SQL-Server-database-to-your-workbook-22c39d8d-5b60-4d7e-9d4b-ce6680d43bad
0
PhonebuffAuthor Commented:
@MIchael,   Not an option unfortunately --  

      So I get this tree  when I unzip the xlsx

      The majority of the "strings" are in sharedStrings.xml and the rest of the data in the  sheet?.xml files.

       If no one is familiar with this I will just keep digging till I get there -

      Thanks -



-rw-r--r--  1 jms  1002  29915 Apr 10 11:03 April 2015 Stats.xlsx
-rwxr-xr-x  1 jms  1002   1854 Jan  1  1980 [Content_Types].xml

_rels:
total 22
drwxr-xr-x  2 jms  1002    3 Apr 12 13:37 .
drwxr-xr-x  5 jms  1002    7 Apr 12 13:37 ..
-rwxr-xr-x  1 jms  1002  588 Jan  1  1980 .rels

docProps:
total 26
drwxr-xr-x  2 jms  1002    4 Apr 12 13:37 .
drwxr-xr-x  5 jms  1002    7 Apr 12 13:37 ..
-rwxr-xr-x  1 jms  1002  828 Jan  1  1980 app.xml
-rwxr-xr-x  1 jms  1002  623 Jan  1  1980 core.xml

xl:
total 98
drwxr-xr-x  8 jms  1002    12 Apr 12 13:37 .
drwxr-xr-x  5 jms  1002     7 Apr 12 13:37 ..
drwxr-xr-x  2 jms  1002     3 Apr 12 13:37 _rels
-rwxr-xr-x  1 jms  1002   722 Jan  1  1980 calcChain.xml
drwxr-xr-x  3 jms  1002     4 Apr 12 13:37 drawings
drwxr-xr-x  2 jms  1002     3 Apr 12 13:37 media
drwxr-xr-x  2 jms  1002     3 Apr 12 13:37 printerSettings
-rwxr-xr-x  1 jms  1002  9128 Jan  1  1980 sharedStrings.xml
-rwxr-xr-x  1 jms  1002  4783 Jan  1  1980 styles.xml
drwxr-xr-x  2 jms  1002     3 Apr 12 13:37 theme
-rwxr-xr-x  1 jms  1002   634 Jan  1  1980 workbook.xml
drwxr-xr-x  3 jms  1002     6 Apr 12 13:41 worksheets

cd xl

-rwxr-xr-x  1 jms  1002   722 Jan  1  1980 calcChain.xml
-rwxr-xr-x  1 jms  1002  9128 Jan  1  1980 sharedStrings.xml
-rwxr-xr-x  1 jms  1002  4783 Jan  1  1980 styles.xml
-rwxr-xr-x  1 jms  1002   634 Jan  1  1980 workbook.xml

_rels:
total 22
drwxr-xr-x  2 jms  1002     3 Apr 12 13:37 .
drwxr-xr-x  8 jms  1002    12 Apr 12 13:37 ..
-rwxr-xr-x  1 jms  1002  1113 Jan  1  1980 workbook.xml.rels

drawings:
total 30
drwxr-xr-x  3 jms  1002     4 Apr 12 13:37 .
drwxr-xr-x  8 jms  1002    12 Apr 12 13:37 ..
drwxr-xr-x  2 jms  1002     3 Apr 12 13:37 _rels
-rwxr-xr-x  1 jms  1002  3788 Jan  1  1980 drawing1.xml

media:
total 30
drwxr-xr-x  2 jms  1002     3 Apr 12 13:37 .
drwxr-xr-x  8 jms  1002    12 Apr 12 13:37 ..
-rwxr-xr-x  1 jms  1002  8971 Jan  1  1980 image1.png

printerSettings:
total 26
drwxr-xr-x  2 jms  1002     3 Apr 12 13:37 .
drwxr-xr-x  8 jms  1002    12 Apr 12 13:37 ..
-rwxr-xr-x  1 jms  1002  7992 Jan  1  1980 printerSettings1.bin

theme:
total 26
drwxr-xr-x  2 jms  1002     3 Apr 12 13:37 .
drwxr-xr-x  8 jms  1002    12 Apr 12 13:37 ..
-rwxr-xr-x  1 jms  1002  6995 Jan  1  1980 theme1.xml

worksheets:
total 63
drwxr-xr-x  3 jms  1002      6 Apr 12 13:41 .
drwxr-xr-x  8 jms  1002     12 Apr 12 13:37 ..
drwxr-xr-x  2 jms  1002      3 Apr 12 13:37 _rels
-rwxr-xr-x  1 jms  1002   6204 Jan  1  1980 sheet1.xml
-rwxr-xr-x  1 jms  1002   9210 Jan  1  1980 sheet2.xml
-rwxr-xr-x  1 jms  1002  14387 Jan  1  1980 sheet3.xml

Open in new window

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

broro183Commented:
Why do you need to break into the xml structure?

Must the file be xlsx or can it be changed to allow inclusion of macros eg xlsm or my general preference of xlsb?

If the data is in text files can they be imported?
Or is the structure not consistent?

Would it be possible for you to read from the text file "in memory" & process the text before placing the desired information into the spreadsheet?
Here are some snippets from old code that processed the text file in memory:
'RB: extract sourced from Private Sub ImportTextFiles() which is stored in TIQQIT v70.
    'loop through all the chosen text files
    For FileNumInd = LBound(FilesToRead) To UBound(FilesToRead)
        FileToOpen = FilesToRead(FileNumInd)
        Set FSO = CreateObject("Scripting.FileSystemObject")
        Set TextFile = FSO.OpenTextFile(FileToOpen, 1, False, -2)
'...
        'initial boolean checks on the whole text file to prevent errors in subsequent processing
        '(ie check for end of file or missing search strings!)
        With FSO.OpenTextFile(FileToOpen, 1, False, -2)
            WholeFileAsStr = .ReadAll
            .Close
        End With
'...
        Qry_D_Exists = InStr(1, WholeFileAsStr, "Query: ") > 0
'...
        WholeFileAsStr = vbNullString
        '****
        If Not Qry_Already_Exists_In_Sheet Then
            CurLine = TextFile.ReadLine
            'Pick up the Query details from the text string of the current line of the read textfile
            With QryD
                .Name = SelStrUsingStartAndEnd(CurLine, "Query: ", False, " Library:", False)

' involving a separate function:
Private Function SelStrUsingStartAndEnd(TextStr As String, StartId As Variant, StartIsPos As Boolean, _
                                        EndId As Variant, EndIsPos As Boolean, Optional LeaveDotsInTheStr As Boolean = False) As String
'Obj: This function returns a string, by using the parameters which
'are passed to the function as arguments. The function uses Optional flags
'(StartIsPos (eg "start is a position" = true/false) & EndIsPos) to differentiate b/n a numerical
'character location or a text phrase being used to identify the start & the end postions
'(StartPos & EndPos) of the resultant string. The resultant string is returned from within the
'TextStr which the function receives as an argument (where the TextStr is a line from the textfile).
Dim StartPos As Long, EndPos As Long
    'define the locations of the start & end of the resultant text string
    If StartIsPos Then
        StartPos = StartId
    Else
        StartPos = InStr(TextStr, StartId) + Len(StartId)
    End If
    If EndIsPos Then
        EndPos = EndId
    Else
        EndPos = InStr(TextStr, EndId)
    End If
    'use the start & end locations to define the resultant text string
    If (StartPos = 0 And EndPos = 0) Or (StartPos > EndPos) Then
        SelStrUsingStartAndEnd = vbNullString
    Else
        'the next test is needed to prevent file names having the dot removed (eg prevents "cd_cog_idt.txt" from becoming "cd_cog_idttxt")
        If LeaveDotsInTheStr Then
            SelStrUsingStartAndEnd = Trim$(Mid$(TextStr, StartPos, (EndPos + 1) - StartPos))
        Else
            SelStrUsingStartAndEnd = Trim$(Replace$(Mid$(TextStr, StartPos, (EndPos + 1) - StartPos), ".", vbNullString, 1, , vbBinaryCompare))
        End If
    End If
End Function

Open in new window


Hth
Rob
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PhonebuffAuthor Commented:
Why do you need to break into the xml structure?

Must the file be xlsx or can it be changed to allow inclusion of macros eg xlsm or my general preference of xlsb?

The data is on a FreeBSD box in a very large relation database.   The Worksheet is an executive summary snapshot and is to be distributed weekly out of a CRON job via e-mail directly to the principals.  

NO WINDOWS, NO EXCEL SOFTWARE, NO PEOPLE.  

I do have this mostly figured out now and I do appreciate the advise to date.
0
Michael FowlerSolutions ConsultantCommented:
Can you make the text file available to the machine which has excel eg Samba drive

This way a macro like the one suggested by @broro183 would work

From what you are describing, it may be possible but it will be difficult to maintain

That said XLSX is an extension of the Office Open XML File Formats. Here is the reference to that standard which may help you
http://www.iso.org/iso/iso_catalogue/catalogue_tc/catalogue_detail.htm?csnumber=59575
0
PhonebuffAuthor Commented:
The answers will help me and others with future projects, but this one is still an XML file that I am working with, as there is no other option available..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.