Results 1 to 3 of 3

Macro to read txt files as input and output as xlsx in user specified directories

Threaded View

  1. #1
    Registered User
    Join Date
    09-16-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    2

    Macro to read txt files as input and output as xlsx in user specified directories

    Dear all, I have been working for this macro for few days. The issue i faced is that whenever the macro tries to save the file, error '1004' occurs.
    here i attach the coding for my macro.

    Workbooks.OpenText Filename:=indir1, Origin:=936, _
            StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
            Space:=False, Other:=True, OtherChar:="^", FieldInfo:=Array(1, 1), _
            TrailingMinusNumbers:=True
        Cells.Select
        Selection.Copy
        Workbooks.Add.SaveAs Filename:=outdir1
        ActiveSheet.Paste
        Application.CutCopyMode = False
        
        ActiveWorkbook.Save
        ActiveWindow.Close
        ActiveWindow.Close
    indir1 is a string and its value is ""C:\assignment\test1.txt"" , the input file path
    indir2 is a string with value ""test1.txt"" , the input filename
    outdir2 is a string with value ""C:\assignment\test1.xlsx"" , the output file path

    I cannot fix the output directories as it is a user specified location.
    The error I got is
    'Run-time error '1004':

    The file could not be accessed. Try one of the following:
    1. Make sure the specified folder exists.
    2. Make sure the folder that contains the file is not read-only.
    3. make sure the file name does not contain any of the following characters: < > ? [ ] : | or *
    4. make sure the file/ path name doesn't contain more than 218....'


    Any ideas how to workaround with this issue?
    Any help is greatly appreciated!!
    Last edited by Cutter; 09-18-2012 at 09:38 AM. Reason: Added code tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1