Linked spreadsheet filepath query r.e. saving?

[pangster]

Hi all,

I have a quick question that i'm hoping some of you guys can help me with.

I have a target workbook which has 3 cells on the first sheet which detail:

the filepath (i.e.: \\Desktop\Action Plan Trackers) = H13
the spreadsheet name (i.e.: MAIN Tracker v1.3.xls)= H14
and the sheet name (i.e.: Business Case) = H15

At the moment this works well and it imports the data from the source workbook. Ultimately i want to use the target workbook for more than one source (not at the same time) - (the target workbook prompts to be saved after the import has been completed) and wanted to know if there was an easy way of automatically updating the filepath in the target workbook with the details from the source workbook

I kind of had an idea that the agent completes the data in the source workbooks - triggers a macro off of a command button i.e. Export - this then opens the Target workbook (which updates H13, H14 and H15) with the details of the source workbook automatically, the import macro then completes and the user is prompted to save the workbook with the correct name...

I'm not sure how easy/hard this would be to achieve though? - so any help would be fantastic!!!

 

[pangster]

Hi again
I guess you're in a different timezone, US? would have replied sooner

First thought, and this sounds really stupid, is the name of the proc you're calling GetValue_ExecutiveSummaryUpdate
spelt correctly in the sub GetValue_ExecutiveSummaryUpdate declaration?

I copied your code, commented out the bits which wouldn't work without the files, and got the same error for exactly that reason

The only other thing I can think of is, where is the GetValue_ExecutiveSummaryUpdate macro? In the same module? If not, try it in there

Hi Johnny,

Thanks for the reply! - the macro is spelt correctly (i copied and pasted it to make sure! lol!) and there are no spaces etc

The "GetValue_ExecutiveSummaryUpdate" macro is in the target workbook. I need the following to happen with the macro:

1 = Export button pressed in source workbook, export macro is executed (COMPLETED! )
2 = Filepath details and Filepath names are updated in source workbook (COMPLETED! )
3 = Target workbook opens (COMPLETED! )
4 = Details from step 2 are copied into Target workbook (as they are referenced in macro in Target workbook) (COMPLETED! )
5 = Macro "GetValue_ExecutiveSummaryUpdate" is triggered in Target workbook and correct values are imported from source workbook (This is where the problem is...)

i thought this would be easy! lol!...

 

[Johnny C]

Well, if you've put the code that opens the target workbnook into a separate macro sheet, put the GetValue_ExecutiveSummaryUpdate code in the macro workbook too. It shouldn't matter where it is once it's successfully called.

The advantage of putting it in the macro sheet, not only will it work (hopefully!) but if you need to change the way it works in the future, you will only need to change the code once.

One tip - rather than used cell references in the code (e.g. "H13"), use Cells(13, 8).value type references. Several reasons;
1) if you wish to try and process a lot of files, it's easier to loop used the CELL(Y,X) form.
2) You can parametrise the cell references;

I have set workbooks up for people where graphs are used that update dynamically based on number of month's data, and they need to be able to add graphs. They aren't people who can understand code or even set charts up (very basic users of Excel). So I set them up with a control sheet where they can specify the number of the row for a line of data for the chart and rows/columns in source files where data can be picked up from. They just type in the number of the row, the macro picks up the row and uses it with cell(row,col).value to create a graph. The chart uses ranges for data, so they also specify a name with no punctuation and the macro creates a named range using the name and row number and creates the graph for them.

 

[pangster]

Well, if you've put the code that opens the target workbnook into a separate macro sheet, put the GetValue_ExecutiveSummaryUpdate code in the macro workbook too. It shouldn't matter where it is once it's successfully called.

The advantage of putting it in the macro sheet, not only will it work (hopefully!) but if you need to change the way it works in the future, you will only need to change the code once.

One tip - rather than used cell references in the code (e.g. "H13"), use Cells(13, 8).value type references. Several reasons;
1) if you wish to try and process a lot of files, it's easier to loop used the CELL(Y,X) form.
2) You can parametrise the cell references;

I have set workbooks up for people where graphs are used that update dynamically based on number of month's data, and they need to be able to add graphs. They aren't people who can understand code or even set charts up (very basic users of Excel). So I set them up with a control sheet where they can specify the number of the row for a line of data for the chart and rows/columns in source files where data can be picked up from. They just type in the number of the row, the macro picks up the row and uses it with cell(row,col).value to create a graph. The chart uses ranges for data, so they also specify a name with no punctuation and the macro creates a named range using the name and row number and creates the graph for them.

i've got it working completely now!!!! i'm so happy! I was lucky that you and Andrew Poulson helped! - here's the completed working code:

Sub RunExport()

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

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

'This code opens up the Business Case Template file - update location as required
myDir = "\\na-13\JOE BLOGGS\Desktop\ACTION PLAN TRACKERS\Business Case"
CreateObject("WScript.Shell").CurrentDirectory = myDir

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

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")

MsgBox "Please be patient whilst the macro generates your Business Case"

Application.Run "'" & TargWB & "'!Update"

'This code prompts the user to save the workbook as a name of their choice
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If fileSaveName <> False Then
ActiveWorkbook.SaveAs Filename:=fileSaveName
End If

End Sub

so in summary the above code achieves everything i wanted it too!!:

1. agent triggers above macro by pressing "Export" button on Business Case sheet
2. Business Case template (target workbook) is opened
3. file location details for source workbook are updated on H13, H14 and H15 in target workbook
4. import macro is executed and Business Case template is updated with all the details required from the source workbook
5. Once import macro completes - agent is prompted to save file

 

[Johnny C]

Nice one

Couple of bits (presumably from Andrew) that I'll take a note of - the getsaveas filename and the macro running from another workbook.

 

[pangster]

Nice one

Couple of bits (presumably from Andrew) that I'll take a note of - the getsaveas filename and the macro running from another workbook.

Yep Andrew helped with the formatting/syntax for running the macro - that was a major PITA to get right and i got the workbook save details from the VBA help! lol!..

thanks again for your help with this one! i'm just chuffed i got it finished/working!

 

[Johnny C]

Your VBA help is probably more uptodate than mine

One thing worth considering if you're getting into VBA a bit more - you didn't declare any variables with DIM statements. I know you don't need to, Excel figures them out at runtime, but you may find with complicated macros that they help debugging. They give 'type mismatch' errors when you assign the wrong type of data to them, but sometimes without them a macro goes wrong and you can't figure out why, and declaring things means you get an error earlier and in a place where you can figure out that it's the type that's caused a problem. You need to understand the types, for example integers only go up to 32,000 or so which means if you're using an integer for a row that goes down rows it will suddenly stop at 32K. Declaring them also makes the memory requirements more efficient, type variant (which is the default) takes up a lot of memory if you have a lot of them. You don't need to type out the typename, there are symbol shortcuts, the simple ones you use most are

integer - variablename%
string - variablename$
long (integer > 32K) - variablename&
single (floating point decimals) - variablename!
double (extra long floating point decimals) - variablename#
Currency(4 decimal places fixed) - variablename@

Boolean, data and object don't have a shortcut (in Excel 2003)

 

[pangster]

Your VBA help is probably more uptodate than mine

One thing worth considering if you're getting into VBA a bit more - you didn't declare any variables with DIM statements. I know you don't need to, Excel figures them out at runtime, but you may find with complicated macros that they help debugging. They give 'type mismatch' errors when you assign the wrong type of data to them, but sometimes without them a macro goes wrong and you can't figure out why, and declaring things means you get an error earlier and in a place where you can figure out that it's the type that's caused a problem. You need to understand the types, for example integers only go up to 32,000 or so which means if you're using an integer for a row that goes down rows it will suddenly stop at 32K. Declaring them also makes the memory requirements more efficient, type variant (which is the default) takes up a lot of memory if you have a lot of them. You don't need to type out the typename, there are symbol shortcuts, the simple ones you use most are

integer - variablename%
string - variablename$
long (integer > 32K) - variablename&
single (floating point decimals) - variablename!
double (extra long floating point decimals) - variablename#
Currency(4 decimal places fixed) - variablename@

Boolean, data and object don't have a shortcut (in Excel 2003)

Johnny - thanks for the help/tips! - i seriously need to start reading more and practicing more with VB to get a better understanding.. most of what i do/use at the minute tends to be mix and match code from other macros etc..

in fact that could be a new years resolution for me! lol! - to learn more VBA!! lol!