Directing VBA to create a Temp folder with path of where the files is run from

[bdouglas1011]

I have a macro that when run.. it looks to see if a temp folder exists within a folder my excel sheet is run from... but wont actually create it if a temp file does not exist ....I have to manually create one with the folder.

Also I use this folder on different machines so I would like to change the actual directory of the Temp file to be the directory of the folder in which the excel file resides in (Remember we use this on different machines I dont want other to have to manually chnage the code.

Please Login or Register  to view this content.

When this macro runs it takes graphs on worksheet "SurveyQC" & places those pics in that Temp folder and then when it formats my email body it pulls those pics from that Temp folder and places them in the email

How can I change the directory to just point to the active directory and then when it creates the Filename1 pics instead of saving to a Folder path that never changes ... direct it to the Temp folder path it creates.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[bakerman2]

How about ?

Please Login or Register  to view this content.

[bdouglas1011]

That works for the TempDir now
It creates the Pics in the temp directory and attaches them but the only ones it puts on the email body are the last 3 not the first one highlighted in red


' Export the SurveyQC Charts
'FileName1 = "C:\Users\GTMWD\Desktop\ETX_LF170045_Sealy 2H\Temp\TGF.png"
FileName1 = ThisWorkbook.Path & "\Temp\" & "\TGF.png"
FileName2 = ThisWorkbook.Path & "\Temp\" & "\TMF.png"
FileName3 = ThisWorkbook.Path & "\Temp\" & "\MDip.png"
FileName4 = ThisWorkbook.Path & "\Temp\" & "\MagXY.png"

Sheets("SurveyQC").ChartObjects("Chart 1").Chart.Export FileName1
Sheets("SurveyQC").ChartObjects("Chart 2").Chart.Export FileName2
Sheets("SurveyQC").ChartObjects("Chart 3").Chart.Export FileName3
Sheets("SurveyQC").ChartObjects("Chart 5").Chart.Export FileName4

' Build the body of the email here

EmailBody1 = RangetoHTML(rng4) & "<br>" & RangetoHTML(rng5) & "<br>" & RangetoHTML(rng6) & _
"<br><img src=ThisWorkbook.Path\Temp\TGF.png" & _
"<br><img src='C:\Users\GTMWD\Desktop\ETX_LF170045_Sealy 2H\Temp\TMF.png'>" & _
"<br><img src='C:\Users\GTMWD\Desktop\ETX_LF170045_Sealy 2H\Temp\MDip.png'>" & _
"<br><img src='C:\Users\GTMWD\Desktop\ETX_LF170045_Sealy 2H\Temp\MagXY.png'>"

[/code]

[bakerman2]

Please use Code Tags when posting code.
To do so highlight your code and click #

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[bdouglas1011]

Sorry I forgot the first code tag. I made the changes but still the pics don't paste in the body. The files are the folder and they even attach to the email but the actual pics dont come up anymore

[bakerman2]

This should be better.

Please Login or Register  to view this content.

[bdouglas1011]

No luck when i run it I get the Email Body RNG(4,5,6) but after that when it should show the charts it is blank. when i use the direct path it works fine

I attached all the code again in case I Missed something

this is working but if I switch to your email body part it wont.

Please Login or Register  to view this content.

should I try to attach folder with file in it & you try and run it.

[bakerman2]

Maybe best because it works fine for me.
Tested with this.

Please Login or Register  to view this content.

[bdouglas1011]

OK here is the weird part I copied my folder and changed the name to Test.

Then I opened the folder and selected the excel file and ran the macro.....and low and behold it works.

But when I run it in the other folder it does not show the graphs ....what in the world would make that happen


If I just change the name of the folder my excel file is say to "test" it works but change the name back to what it should be it does not.

[bakerman2]

Is TempDir where you store the images the same as when you add them to your emailbody.
I ask because earlier you spoke of Temp2 and in your latest codes it's Temp.

[bdouglas1011]

Yes the temp folder is now just called temp not temp2 with in the main folder

If I take the main folder and do nothing but rename to "Test" and then open my excel file it works.

But if keep the file named "ETX_LF170045_Sealy #2H" it will not work

I figured it out.....

Changed folder name from "ETX_LF170045_Sealy #2H"

to "ETX_LF170045_Sealy_#2H"

I used _ between Sealy & #2H.......in the folder name and that worked......

I have no idea why if you do let me know why.

[bakerman2]

# is an illegal character that can't be used in a name. Connecting them probably made it part of the string instead of being recognised as a seperate character.

[bdouglas1011]

I tested that theory and it did not work....i may never know why this happened.

Thanks for your help