How to concatenate a variable with a Range

**Glio** · 02-28-2008, 04:38 PM

Following advice from this forum I have developed the following code to save a workbook with a filename made up from Ranges within a worksheet. What I had also hoped to do was include the first 2 letters of a Range.

The code that works is as follows

Please Login or Register  to view this content.

This code includes the line

Please Login or Register  to view this content.

which I believe creates a variable Ex made up of the first 2 characters of the Range ExIncOp.
How do I then introduce this variable into the ActiveWorkbook line?
I have tried

Please Login or Register  to view this content.

but I realise Ex is not a Range so this fails.

**Leith Ross** · 02-28-2008, 05:14 PM

Hello Geoff,

If ExIncOp is the name of Range then you need to change your code to this...

Please Login or Register  to view this content.

Left requires 2 arguments: a literal string or string variable, and the number of characters to return.

Sincerely,
Leith Ross

**cremate** · 02-28-2008, 06:07 PM

Try to explain again what you're trying to achieve; perhaps with example input/output.

**Glio** · 02-29-2008, 05:12 AM

Hi Leith / cremate,

I had already found the error in lack of " " and the code as follows works

Please Login or Register  to view this content.

But if I include the highlighted red code as follows

Please Login or Register  to view this content.

it fails even if I take the " " from around Ex

Regards

**Glio** · 02-29-2008, 11:05 AM

I have a template worksheet where the user enters data in three cells which are also named Ranges.

I have a button on the sheet which should save the file with the first two letters of the first Range, a space, the second Range, a space, and the third Range.

The file name should look something like In Test 29-Feb-08.xls.

The code I have works without the first two letters of the first Range.

Please Login or Register  to view this content.

I would like to add those two letters to the file name by replacing the red code with something like

Please Login or Register  to view this content.

The magenta code is my addition, which does not work

Can anyone see the error?

Thanks in advance

**Leith Ross** · 02-29-2008, 12:02 PM

Hello Geoff,

In your code the variable EX is a string. If I understand what your are trying to do, you want the first 2 letters of the value stored in the Named Range "ExIncOp" to the file title. The changes are in blue.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**Glio** · 03-03-2008, 04:14 AM

Leith,

Thanks for your response, which made complete sense to me and you understood exactly what I required, however I still get the following error

Run-time eror '1004'
Method 'Range' of object '_Worksheet' failed

I attach the .xlt file and hope you can see what is going wrong. If you cannot, I will be happy without the first 2 letters of Range 'ExIncOp'!

Regards

**Leith Ross** · 03-03-2008, 04:47 AM

Hello Geoff,

Your file has only one worksheet named "Operation". The other two worksheets "Chronology" and "Post Event" are missing. The error occurs because CmdBtn3 is supposed to be located on the worksheet "Chronology" which is missing. You need the other sheets before the command button initialization will work correctly.

Sincerely,
Leith Ross

**Glio** · 03-03-2008, 05:38 AM

Hello Leith,

I was trying to keep the file size small and forgot about the other links!

Here is the Workbook with all the sheets.

Thanks for your help, again

**Glio** · 03-04-2008, 09:56 AM

BUMP

for Leith Ross

**Leith Ross** · 03-04-2008, 11:19 AM

Hello Geoff,

Thanks for the bump. I work on it first thing this morning (just woke up),

Thanks,
Leith Ross

**Leith Ross** · 03-04-2008, 03:32 PM

Hello Geoff,

I have a question about this part of your code below.

Please Login or Register  to view this content.

Since ExIncOp refers back to Operations!$C$6, did you want the first 2 letters contained in this cell to used as part of the workbook name? Ignoring [Select] of course.

Thanks,
Leith Ross

**Glio** · 03-05-2008, 05:12 AM

Hi Leith,

I've just woken up too!

Yes, I do want the first 2 letters of the contents of Operation!$C$6 to be included in the file name.

i.e. If Operation!C6 = Exercise

Please Login or Register  to view this content.

Operation!H6 = Test

Please Login or Register  to view this content.

Operation!E10 = 03-Mar-08

Please Login or Register  to view this content.

the File name should be Ex Test 03-Mar-08.xls

Hope that is clear!!

Many thanks for your help so far

Regards

**Coldpaws** · 03-05-2008, 08:38 AM

Hi Glio,
I have had a look at your example file and I think I understand your requirement. However no matter what I tried I could not get any of your concatenated file path to save the file if the folders did not already exist .
I have modified the approach to use the make directory function MkDir to create the file path in the correct order then save the dated file .
I've also attached my example file for reference.

BTW - to moderators , this is my first code post on the forum , I hope i've posted the correct code tags etc , if not please advise.

See if it works for you Glio
Regards.

Please Login or Register  to view this content.

**Coldpaws** · 03-05-2008, 10:15 AM

Glio
I saw that you actually want the filename containing Ex and the name and the date all in the same debrief folder . Sorry , I was rushing it during my lunch! My previous example will work but will create you a whole new directory structure to store the reports by date

The attached new example still retains the check that the debrief folder exists and creates it if not but the uses a variable to create the file name and then save it
Hope this helps

**Leith Ross** · 03-05-2008, 01:59 PM

Hello Geoff,

As Coldpaws pointed out, you use a value from a named range in your SaveAs file name that isn't declared in your project: Debrief_Folder. Which sheet and what cells are supposed to make up this range?

Sincerely,
Leith Ross

**Glio** · 03-05-2008, 06:35 PM

Hi Leith,

The Range Debrief_Folder is in the file Register.xls which is the same folder as the XLT file. Maybe I cannot refer to another workbook, or that workbook has to be open to be able to access the Range?

I had a brief try at Coldpaws suggestions and got part of it working before I had to go out. I will try further tomorrow.

Thanks for all your efforts.

Regards

**Glio** · 03-07-2008, 09:32 AM

Coldpaws,

Many thanks for your two versions! I copied the hide button code into the second version and today I have managed to get it all working exactly as I wanted. BRILLIANT!

Regards