Linked spreadsheet filepath query r.e. saving?

Johnny C said:

it sounds fairly simple, if I understand your question. Apologies if I don't!

All you would need is to make the command button run a sub in the target file (so you create the VBA in target, put a button in source and assign the target VBA sub to the button.

Then you could just pick up the 3 cells of data from the source and use workbooks(target).sheet(sheetname).cells(13,8).value = data (H13).

the sourcebook name would be picked up by activeworkbook.name whilst the source file was still active (i.e. before activating target) - you need to do that first or you'd have problems if the agent had multiple workbooks open.

If you know the name it should be saved as, don't prompt the agent to save it - create the path and filename and save it within the code. Giving a human agent the opportunities to save it with the wrong name will just mean that people occasionally save it with the wrong name.

Click to expand...

Johnny C said:

Okay.

What you really need to do is create a macro file. The name of this needs to be specified in the macro, as you need to get the macro to close the macro file when the export is done. I tried putting the macro into the target file and that made it chuck a wobbler, opening the file interrupted the macro. So you need a separate macro file. Attach the macro to a button on the toolbar, it will open the macro file whilst the macro runs but the macro closes itself using the name specified in the macro - so make sure they are the same

The macro (in the macro file) creates a blank file (or you can open an existing template)

The copying names to cells is easy.

The hard bit comes in saving the file. Now it may be the weird setup I have here (our IT dept puts all sorts of dodgy Office patches in) but I can't get it to prompt for a filename, and I'm sure I've done it before. The macro saves a blank book as <blankbook.xls> in the current folder. I tried saveas, I tried savecopyas. I tried opening a template sheet as readonly and saving it to try and open a conflict resolution box but that crashed it. Maybe someone else can help, maybe it's just my setup, so try it yourself. If you can't force it to show a save as dialog, you'll have to either use an inputbox to get the user to enter a filename, directory etc, or else generate it yourself.

Code:

macrofile = "macro.xls"
sourcefilename = ActiveWorkbook.Name
sourcepath = ActiveWorkbook.Path
sourcesheet = ActiveSheet.Name

then either

Code:

Workbooks.Add

or

Code:

Workbooks.Open Filename:="C:\Book12.xls"

the next few lines put the data you want into the cells you want (H13:15)

Code:

targetfile = ActiveWorkbook.Name

Workbooks(targetfile).Activate
Cells(13, 8).Value = sourcepath
Cells(14, 8).Value = sourcefilename
Cells(15, 8).Value = sourcesheet

'     call import routine

Workbooks(targetfile).Activate
Workbooks(targetfile).Save
Workbooks(macrofile).Close

Hope this helps

Click to expand...

Sub Button_BUSINESS_CASE_EXPORT()

'This code copies the open workbook details (source workbook) to cells C84 to C86 in the source Business Case sheet
Sheets("Business Case").Select
Range("C84") = ActiveWorkbook.Path
Range("C85") = ActiveWorkbook.Name
Range("C86") = ActiveSheet.Name

'This code opens up the Business Case Template file
myDir = "C:\Documents and Settings\JOE BLOGGS\Desktop\ACTION PLANS"
CreateObject("WScript.Shell").CurrentDirectory = myDir

' update filename here
Workbooks.Open Filename:=myDir + "\Business Case template v1.0.xls"

OrigWB = Sheets("Business Case").Range("C85").Value
OrigWS = Sheets("Business Case").Range("C86").Value

TargWB = "Business Case template v1.0.xls"
TargWS = "Executive Summary"

Workbooks(TargWB).Sheets(TargWS).Range("H13:H15").ClearContents

Workbooks(OrigWB).Sheets(OrigWS).Range("C84").Copy Workbooks(TargWB).Sheets(TargWS).Range("H13")
Workbooks(OrigWB).Sheets(OrigWS).Range("C85").Copy Workbooks(TargWB).Sheets(TargWS).Range("H14")
Workbooks(OrigWB).Sheets(OrigWS).Range("C86").Copy Workbooks(TargWB).Sheets(TargWS).Range("H15")

Workbooks(TargWB).Sheets(TargWS).Select

Call GetValue_ExecutiveSummaryUpdate

End Sub

Click to expand...