Copying module from workbook to workbook

[marc81]

Hi all,

I have an Excel tool that pulls some data from SAP and compiles it together in a workbook, it then creates an extract in to another workbook. As part of this a macro module is copied in to the new workbook. This all worked fine but I think a recent Microsoft update has caused issues when running from a One Drive location. I've put a fix in place which now works perfectly for me, but for other users it doesn't work - specifically the line where it exports the strTempFile (~tmpexport.bas), it isn't being created for other users. Can anyone see any issues? Sorry for the messy code, I'm a bit of an amateur when it comes to VBA and usually just use bits of code I can find an manipulate

Thanks


Dim strModuleName As String
Dim strFolder As String
Dim strTempFile As String

ThisWorkbook.Activate '<= changed

strFolder = ThisWorkbook.Path '<= changed
If Left(strFolder, 4) = "http" Then
strFolder = CStr(Environ("USERPROFILE")) 'fix for onedrive locations
End If
If Len(strFolder) = 0 Then strFolder = CurDir
strFolder = strFolder & "\"
strTempFile = strFolder & "~tmpexport.bas"
On Error Resume Next
ThisWorkbook.VBProject.VBComponents("Alloc_Date_Button").Export strTempFile '<= changed

'AMEND THE EXPORTED MACRO TO NEW NAME
Dim objFSO
Const ForReading = 1
Const ForWriting = 2
Dim objTS 'define a TextStream object
Dim strContents As String
Dim fileSpec As String

fileSpec = ThisWorkbook.Path & "\~tmpexport.bas"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTS = objFSO.OpenTextFile(fileSpec, ForReading)
strContents = objTS.ReadAll
strContents = Replace(strContents, "Alloc_Date_Button", "Alloc_Date_Button_Export")
strContents = Replace(strContents, "ALLOC_DATE_UPDATE", "ALLOC_DATE_UPDATE_EXPORT")

objTS.Close

Set objTS = objFSO.OpenTextFile(fileSpec, ForWriting)
objTS.Write strContents
objTS.Close


Output.VBProject.VBComponents.Import strTempFile '<= changed
Kill strTempFile
On Error GoTo 0

 

[rlv01]

The environment var USERPROFILE is likely specific to your PC. Most PCs are set up to have an environment variable "TEMP" that points to a folder for temporary files. Try using that instead.
Replace

strFolder = CStr(Environ("USERPROFILE")) 'fix for onedrive locations

With this:

strFolder = VBA.Environ("TEMP") 'fix for onedrive locations

(Tip: For future posts , please try to use code tags like I did above when posting code. It makes your code easier to read and copy.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

)