Directory VBA does not work.. please help..

[shelim481]

Please Help im having trouble with following code, the only issue is the code that is bold, FlName = Path & c1 & "-" & c2 & ".txt", problem is that "path" does not work, for some reason it does not find the directory of the file...but when i replace "Path" with the actual directory it works fine...

Option Explicit


Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long


Private Const MAX_PATH As Long = 260


Function TempPath() As String
TempPath = String$(MAX_PATH, Chr$(0))
GetTempPath MAX_PATH, TempPath
TempPath = Replace(TempPath, Chr$(0), "")
End Function


Sub Tabs()
Dim tmpFile As String
Dim MyData As String, strData() As String
Dim entireline As String
Dim filesize As Integer
Dim FlName As String
Dim i As Long
Dim Path As String
Path = Application.ActiveWorkbook.Path



For Each c1 In rng1
For Each c2 In rng2
On Error Resume Next



FlName = Path & c1 & "-" & c2 & ".txt"
'~~> Create a Temp File
tmpFile = TempPath & c1 & "-" & c2 & "-" & Format(Now, "hhmmss") & ".txt"

ActiveWorkbook.SaveAs Filename:=tmpFile _
, FileFormat:=xlText, CreateBackup:=False


'~~> Read the entire file in 1 Go!
Open tmpFile For Binary As #1
MyData = Space$(LOF(1))
Get #1 , , MyData
Close #1
strData() = Split(MyData, vbCrLf)


'~~> Get a free file handle
filesize = FreeFile()


'~~> Open your file
Open FlName For Output As #filesize


For i = LBound(strData) To UBound(strData)
entireline = Replace(strData(i), """", "")
'~~> Export Text
Print #filesize , entireline
Next i
Close #filesize

Application.DisplayAlerts = False
Worksheets(Worksheets.Count).Delete
Application.DisplayAlerts = True


Next c2
Next c1
End Sub

 

[Joe4]

You declared path as a string:

[COLOR=#333333]Dim Path As String[/COLOR]

but then you never set it equal to anything!

So, when you call it here:

FlName = Path & c1 & "-" & c2 & ".txt"

you are returning an empty string (for the Path portion).

Did you intend to do this first?

Path = ActiveWorkbook.Path

Also note, that you may need to add the backslash at the end, either here:

Path = ActiveWorkbook.Path [COLOR=#ff0000]& "\"[/COLOR]

or here:

FlName = Path [COLOR=#ff0000]& "\"[/COLOR] & c1 & "-" & c2 & ".txt"

One last note, you shouldn't use reserved words like "Path" as variable names, as it can cause ambiguity and errors.
I would recommend using something like "myPath" or "pth", or just skip it altogether and just use:

FlName = Activeworkbook.Path & "\" & c1 & "-" & c2 & ".txt"

 

[shelim481]

Absolute Legend, thank you

 

[Joe4]

You are welcome.