Compile error in hidden module module 10

Question

i recently created this new module macro   which is a copy  from a similar macro in the same workbook

this one needs to address the fact that everyone's PC  has a different name and a file now has to be created somewhere in the documents folder

It is working great on my 2 PCs   but  my friends keep getting the  COMPILE ERROR

here is the macro

Sub InterData()

'

' macro86 Macro

'

' Keyboard Shortcut: Ctrl+a

'

    Dim CurrentUserName As String

    CurrentUserName = Environ("Username")

    Sheets("TTT Input").Visible = True

    Sheets("TTT Input").Select

    ChDir "C:\Users" & CurrentUserName & "\Documents\Interactive Data\FormulaOutput"

    ActiveWorkbook.SaveAs Filename:= _

        "C:\Users" & CurrentUserName & "\Documents\Interactive Data\FormulaOutput\TTTinput.txt", FileFormat:=xlCSV, _

        CreateBackup:=False

End Sub

I would like to know  where the issue is   since i cant seem to duplicate the problem at my end

Thank you

Answer 1

the code that I posed before should work with all version of excel 2003 and 2010 and on windows XP/Vista/W7

Answer 2

Is your friend has the same sub folders structure?

if other users have Windows XP structure of user folders is different than in Windows 7/Vista and next Interactive Data\FormulaOutput folders must exist

I changed your macro, I added some code to check is all folder exist if not missing folder will be created

Sub InterData()

'

' macro86 Macro

'

' Keyboard Shortcut: Ctrl+a

'

Dim CurrentUserName As String

Dim objFSO As Object

Dim strPath As String

Dim astrPath() As String

Dim sTPath As String

Dim i As Integer

CurrentUserName = Environ("Username")

strPath = "C:\Users" & CurrentUserName & "\Documents\Interactive Data\FormulaOutput"

astrPath = VBA.Split(strPath, "", , vbTextCompare)

sTPath = ""

Set objFSO = CreateObject("Scripting.FileSystemObject")

sTPath = astrPath(LBound(astrPath))

For i = LBound(astrPath) + 1 To UBound(astrPath)

sTPath = sTPath & "" & astrPath(i)

If Not objFSO.FolderExists(sTPath) Then objFSO.CreateFolder (sTPath)

Next

Set objFSO = Nothing

Sheets("TTTInput").Visible = True

Sheets("TTTInput").Select

ChDir "C:\Users" & CurrentUserName & "\Documents\Interactive Data\FormulaOutput"

ActiveWorkbook.SaveAs Filename:= _

"C:\Users" & CurrentUserName & "\Documents\Interactive Data\FormulaOutput\TTTinput.txt", FileFormat:=xlCSV, _

CreateBackup:=False

End Sub

Answer 3

if you uses only office function you can create one macro for all os, if you use some os function you have to create different macros for each os.

Exp. you cannot use Scripting.FileSystemObject on mac os

Again thank you for your help

Yes i am only worried about Excel 2003 to 2010  and the XP/Vista/W7    32 and 64b

 thanks

Answer 4

if you uses only office function you can create one macro for all os, if you use some os function you have to create different macros for each os.

Exp. you cannot use Scripting.FileSystemObject on mac os

Answer 5

Thank you for your reply

I more quick question      is there a way to cover all the different OS in one macro  or will I need more macros

Thanks again