Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Macro to auto increment filename in FileToOpen

    Hi,

    I have atleast 40 text files in a folder and i import it through macro and output is saved.
    The text files are named in sequential order like file1,file2,file3,file4...
    I am planning if the macro automatically increments the filename number then it will import the file without my interferance and save the output with same name to destination folder.


    Code:
    Sub Macro1()
    Dim FileToOpen As String
    FileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")  ' "D:\collect\file1.txt","D:\collect\file2.txt"
    s = Split(FileToOpen, "\") 'USED TO DELETE CONNECTION
    t = Split(s(UBound(s)), ".")  'USED TO DELETE CONNECTION
    If FileToOpen = "False" Then Exit Sub
    With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;" & FileToOpen, Destination:=Range("$A$1"))
    
    :::::: Import commands ::::::::
    
    Some codes to be performed on file
    
    file saving.
    
    ActiveWorkbook.SaveAs Filename:="D:\office\file1.txt", FileFormat:=xlTextMSDOS, CreateBackup:=False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    End Sub

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Look here
    http://www.rondebruin.nl/win/s3/win008.htm
    for
    Merge a range from all workbooks in a folder (below each other)

  3. #3
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi dguillett,

    Actually i want my text files from the folder to be opened individually one at a time and do the process and to be saved, and rerun filetoopen and import file2 and so on.

    Now i have created a path and file name list on sheet1 column A1:A40 (c:\multi\file1.txt)

    FileToOpen = ThisWorkbook.Worksheets("sheet1").Range("A1").Valu e
    Howto after A1 increment to A2 when it reruns the macro.
    Any Helps.

  4. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Goto Ron's site>download the "merge" examples workbook>determine which to modify to your use>. You only learn by YOU doing instead of others doing it for you for free!!

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Foncesa,

    I think you want something like this:
    Code:
    Option Explicit
    
    Sub RetrieveSaveFiles()
    
       Dim wkbCurrent      As Workbook
       Dim zSourcePath     As String
       Dim zDestPath       As String
       Dim zFileBaseName   As String
       Dim zFileExt        As String
       Dim iFileCntr       As Integer
    
       zSourcePath = "D:\collect\"
       zDestPath = "D:\Office\"
       zFileBaseName = "file"
       zFileExt = ".txt"
    
       iFileCntr = 1
       Set wkbCurrent = Workbooks.Open(zSourcePath & zFileBaseName & _
                                                           Format(iFileCntr) & zFileExt)
    
       Do While (Not wkbCurrent Is Nothing)
       
          ProcessFile wkbCurrent
          
          With wkbCurrent
              Application.DisplayAlerts = False
              .SaveAs Filename:=zDestPath & .Name
              .Close
              Application.DisplayAlerts = True
          End With  'ActiveWorkbook
          
          Set wkbCurrent = Nothing
          iFileCntr = iFileCntr + 1
          On Error Resume Next
          Set wkbCurrent = Workbooks.Open(zSourcePath & zFileBaseName & _
                                                              Format(iFileCntr) & zFileExt)
          On Error GoTo 0
          Application.DisplayAlerts = True
          
       Loop
    
    End Sub    'RetrieveSaveFiles()
    
    Sub ProcessFile(ByRef wkbCurrent As Workbook)
    
       Dim s As Variant
       Dim t As Variant
    '
    '  '***** Do All of your processing here  ****
    
       s = Split(wkbCurrent.FullName, "\") 'USED TO DELETE CONNECTION
       t = Split(s(UBound(s)), ".")  'USED TO DELETE CONNECTION
    
    'With ActiveSheet.QueryTables.Add(Connection:= _
    '        "TEXT;" & FileToOpen, Destination:=Range("$A$1"))
    '
    ':::::: Import commands::::::::
    '
    'Some codes to be performed on file
    
    End Sub     'ProcessFile()
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Nicole545 (2014-11-15)

  7. #6
    New Lounger
    Join Date
    Nov 2014
    Posts
    21
    Thanks
    16
    Thanked 1 Time in 1 Post
    Retired Geek,

    I have been looking for a macro to open and close .txt files. This would be perfect and be the answer to many issues if I could find a way to get past the security limitations set by our inflexible IT department. Right now I an doing this manually. Does anyone know of a way to bypass the restrictions?

    Thx
    Nicole

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Nicole,

    Exactly what restrictions?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #8
    New Lounger
    Join Date
    Nov 2014
    Posts
    21
    Thanks
    16
    Thanked 1 Time in 1 Post
    Thanks Retired Geek.

    Sorry, my intent is not to hijack this thread but the restrictions are to run macros.

    Anything you could suggest would be very welcome.
    Nicole

  10. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Nicole,

    The best way to work around a restrictive IT dept. is to make the business case to your boss (time & money saved by being able to use macros) then let him/her fight it out with IT. Usually, the business case will win and there are things IT can do to allow you use macros and still keep the bad stuff away. Providing you with a code signing certificate is one thing that comes to mind then allowing only signed macros to run. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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