Linking Data in different Spreadsheet and sending the summary externally

[penfold]

We currently have a long process of generating spreadsheet reports for AV which I'm trying to automate a bit more. Currently staff log on to indiividual servers and enter details into numerous sheets on a spreadsheet manually and then this is then emailed off. Because of the struicture here there are numerous steps that are needed and I'm not in a position to make changes as I don't really understan the process fully as I've only been here a few weeks.

 

What I am trying to achieve is a summary spreadsheet of numerous reports created on differnt servers. I've created csv files automatically from the servers and then I've managed to link data to from these reports to a summary spreasheet giving an overall total of items. However, if I close them all down when I open the summary spreadsheet I get prompted with "This workbook contains links to one or more external sources that could be unsafe..If you trust the links, update to get the latest data..." If I click on update the values shown have errored and show #Value. The only way the file will update is if I have all the linked files open. If I try do not update then the value shows OK. It seems to be the same issue here

 

What I'm trying to achieve is this process:

Server generates CSV file

Open Summary Spreadsheet template and check values are updated.

Save Summary Spreadsheet with Date to hold all data in single file

Eail Summary to all required parties

 

However, at the minute if I was to try and do this anyone receiving the Summary file would not be able to see the cell content just lots of #Value instead. This must be possible but I can't see what I'm doing wrong?

[ricki]

Is the column wide enough to display the data Richard

[penfold]

Actually this doesn't matter so much as this is a one off process I can put up with having to open all files for the time being. I'm just trying to get a proof of concept. If I manage to link all the data to the summary file, how can I then convert that back to "dumb" data so it uses the data value in the cells and not the formula. What I was thinking is as this would be a repeated task, the template can just be reused, then an exported version of the summary can be renamed Jan, Feb...etc but I don't want linked spreadsheet in this file as it needs to be static for that month. Does that make sense?

 

Edit : @ricki - yes the data displays fine when it is first imported, or the other files are open. All I need to be able to do now is export the template as a static file. Is this possible?

Edited March 27, 2018 by penfold

[ricki]

Select the part of the excel document you want to export click file and export Richard

[ricki]

Exporting to pdf may do what you want

[penfold]

Unfortunately when I export it to PDF it doesn't look quite as good. However, I may be abe to do it by copying the sheet content and paste into a new file with Past>Data which will keep all the relevant data and remove the formulas. I just need to work out a macro that will do this for mutliple sheets so I don't have to do that part manually.

[Marci]

See bottom-most example at https://www.rondebruin.nl/win/s9/win015.htm for breaking links to other external workbooks. Other examples on same page cover converting formula to values.

 

Break only formula links to other Excel workbooks

Sub Break_Links_To_other_Excel_Workbooks()
'This example converts formulas that point to another Excel workbook to values
'It will not convert other Excel formulas to values.
'Note that BreakLink is added in Excel 2002
   Dim WorkbookLinks As Variant
   Dim wb As Workbook
   Dim i As Long

   Set wb = ActiveWorkbook

   WorkbookLinks = wb.LinkSources(Type:=xlLinkTypeExcelLinks)
   If IsArray(WorkbookLinks) Then
       For i = LBound(WorkbookLinks) To UBound(WorkbookLinks)
           wb.BreakLink _
                   Name:=WorkbookLinks(i), _
                   Type:=xlLinkTypeExcelLinks
       Next i
   Else
       MsgBox "No Links to other workbooks"
   End If
End Sub

 

Other variants available at https://www.ozgrid.com/forum/forum/tip-tricks-code/28823-convert-formulas-to-values-instantly-for-the-entire-workbook

Edited March 27, 2018 by Marci

[penfold]

thanks, that looks like it will do what I'm after. Will take a look later

[penfold]

This is exactly what I was looking for. NOw I should be able to do the following:

Create csv files from individual server (Automated)

Link these files to SPreadsheet template

Save as appropraite file nae (Month)

REmove all links with Macro

Email to all interested parties.

 

This should be much quicker once I have put all the styeps into progress.

 

Thanks