Use cell as filename while saving via VBA

Hello All,

I am creating a new script with all kind of examples from the web, because I am a beginner in VBA, but I can't get it right.

Case: Every day I have to create two files from my excel planning sheet, which I save as image on the network. It works now with a hardcopy name, but as I need to copy every day I want to use a cell as part of the filename. This is the script with the hardcoded filename (donderdag 1.jpg) which is working:

Sub Selectie1()
'
' Selectie1 Macro
'

'
Range("B4:U27").Select
Dim tmpChart As Chart, n As Long, shCount As Long, sht As Worksheet, sh As Shape
Dim fileSaveName As Variant, pic As Variant
'Create temporary chart as canvas
Set sht = Selection.Worksheet
Selection.Copy
sht.Pictures.Paste.Select
Set sh = sht.Shapes(sht.Shapes.Count)
Set tmpChart = Charts.Add
tmpChart.ChartArea.Clear
tmpChart.Name = "PicChart" & (Rnd() * 10000)
Set tmpChart = tmpChart.Location(Where:=xlLocationAsObject, Name:=sht.Name)
tmpChart.ChartArea.Width = sh.Width
tmpChart.ChartArea.Height = sh.Height
tmpChart.Parent.Border.LineStyle = 0
'Paste range as image to chart
sh.Copy
tmpChart.ChartArea.Select
tmpChart.Paste
'Save chart image to file
tmpChart.Export FileName:="P:\Planningen\Voor scherm\Donderdag 1.jpg", FilterName:="jpg"
'Clean up
sht.Cells(1, 1).Activate
sht.ChartObjects(sht.ChartObjects.Count).Delete
sh.Delete
End Sub

I want to change Donderdag with the information from cell G4 from the current selected worksheet. So export should be something like:

'Save chart image to file
tmpChart.Export FileName:="P:\Planningen\Voor scherm\(g4)-1.jpg", FilterName:="jpg"
'Clean up

Any help would be appreciated very much!

Kind regards,

Joost

if you want to use cell data, just concat it in. instead of this:

Please Login or Register  to view this content.

do this:

Please Login or Register  to view this content.

if the sheet that has the cell you are target does NOT have the focus or is not the active sheet when the code is run, preceed the RANGE() reference with the sheet object as the qualifying statement. your code already has the book and sheet declared.

Hello Adam,

Thanks for the quick reply. I replaced my statement and tried to run it on the active sheet, but it gave me the error: Error 1004 during running: Methode Range of object_Global is failed. So probably he couldn't find it. I tried to add the sht as active worksheet but as I am a rookie, It didn't work. So I have 52 different sheets, so I want to use the same cell on each sheet when I run that statement, but the sheetname is always variable.

So something like:
tmpChart.Export FileName:="P:\Planningen\Voor scherm\" & RANGE(Activesheet:A1) & ".jpg", FilterName:="jpg"

Hope u can help me again!

Thanks

Kind regards,

Joost

to loop the sheets, do this:

Please Login or Register  to view this content.

put that code in your routine where it should go.

Hello Adam,

Sorry but with this last code it seems to copy the information from the last sheet.

Probably I explained myself incorrectly.

I have copied in an example of 1 sheet of my excelfile. I am on the sheet "47 19 DO" and on the right side I placed a button which is linked to the macro. When I press that button it should take the information from this active sheet cell G4 (text "donderdag") and use that as filename of the saved jpg.

When I go to the next sheet, I have the same button and than it should take the information out of G4 from that sheet, so sheet "47 20 VR"

Attachment 704947

Hope this helps.

I cannot view attachments the way you uploaded yours. I have no idea what question askers do to cause that. see this image. got another way?

I tried again. Can you see the attachment now?

Originally Posted by jesse1601

When I go to the next sheet, I have the same button and than it should take the information out of G4 from that sheet, so sheet "47 20 VR"

if you want to have a button on EVERY SHEET and press those manually everytime you want an export, then it gets even easier:

Please Login or Register  to view this content.

alternatives to try if it errors:

activesheet.name

activeworkbook.activeworksheet

activeworkbook.activesheet

put that code in a standard module and call the same routine behind every button on every sheet.

Looks like Cell G4 does not have "Donderdag"
It does look like the Cell(s) that have "Donderdag" are formatted as Merge & Center.
If so, to avoid future problems which are just about certain to happen, get rid of all Merge & Center and change it to Center Across Selection.
Als dat niet zo is, vergeet wat ik net heb gezegd.

Please Login or Register  to view this content.