Directory VBA does not work.. please help..
Results 1 to 4 of 4

Thread: Directory VBA does not work.. please help..

  1. #1
    New Member
    Join Date
    Aug 2018
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Directory VBA does not work.. please help..

    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

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,443
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Directory VBA does not work.. please help..

    You declared path as a string:
    Code:
    Dim Path As String
    but then you never set it equal to anything!

    So, when you call it here:
    Code:
    FlName = Path & c1 & "-" & c2 & ".txt"
    you are returning an empty string (for the Path portion).

    Did you intend to do this first?
    Code:
    Path = ActiveWorkbook.Path
    Also note, that you may need to add the backslash at the end, either here:
    Code:
    Path = ActiveWorkbook.Path & "\"
    or here:
    Code:
    FlName = Path & "\" & 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:
    Code:
    FlName = Activeworkbook.Path & "\" & c1 & "-" & c2 & ".txt"
    Last edited by Joe4; Jul 2nd, 2019 at 01:50 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Aug 2018
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Directory VBA does not work.. please help..

    Absolute Legend, thank you

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,443
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Directory VBA does not work.. please help..

    You are welcome.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •