locked
VBA code to create n number of txt/doc/xls files with specific size. RRS feed

  • Question

  • Hi all,,,,,,,,,

    Plzzzzzzz help me with the vba code to create n number of files with specific size.

    I have a code which would create text/word/excel files .....But I tried this code to create 'n' number of files....

    To be clear.....I need to create 1000 txt files with the content as 'Hello World1'  'Hello word2'.............'Hello World1000' respectively..........

    And If i specify the size of the file in a text box then it should create the file with that size.....

    But I couldn't make it...................help me this out..........

    The following is the code ........

    Public Sub veeru()    

    Const FILENAME = "C:\Users\veeru\Desktop\myfile.xls"  

     Dim My_filenumber As Integer    

    My_filenumber = FreeFile  

    Open FILENAME For Output As #My_filenumber    

    Write #My_filenumber, "Hello"    

    Close #My_filenumber End Sub


    Veeru

    Tuesday, May 8, 2012 9:22 AM

Answers

  • Open ... Write ... Close can be used to create text files (*.txt), NOT to create Word documents (*.doc or *.docx) or Excel workbooks (*.xls or *.xlsx). To create a Word document, you must use Word, and to create an Excel workbook, you must use Excel.

    Here is a VBA macro that will ask you how many files you want and what size they should be, then create the files and save them to your desktop:

    Sub CreateFiles()
        Dim lngIndex As Long
        Dim lngNumber As Long
        Dim lngSize As Long
        Dim lngFile As Long
        Dim strPath As String
        Dim strFile As String
        Dim strText As String
        lngNumber = Val(InputBox("How many files do you want to create?", , "1000"))
        If lngNumber < 1 Then Exit Sub
        lngSize = Val(InputBox("What file size do you want (in bytes)?", , "1000"))
        If lngSize < 20 Then Exit Sub
        strPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"
        For lngIndex = 1 To lngNumber
            strFile = "File" & lngIndex & ".txt"
            lngFile = FreeFile
            Open strPath & strFile For Output As #lngFile
            strText = "Hello World " & lngIndex & Space(lngSize)
            strText = Left(strText, lngSize - 4)
            Write #lngFile, strText
            Close #lngFile
        Next lngIndex
    End Sub


    Regards, Hans Vogelaar

    • Marked as answer by Veerusat Tuesday, May 8, 2012 10:42 AM
    Tuesday, May 8, 2012 9:48 AM

All replies

  • Open ... Write ... Close can be used to create text files (*.txt), NOT to create Word documents (*.doc or *.docx) or Excel workbooks (*.xls or *.xlsx). To create a Word document, you must use Word, and to create an Excel workbook, you must use Excel.

    Here is a VBA macro that will ask you how many files you want and what size they should be, then create the files and save them to your desktop:

    Sub CreateFiles()
        Dim lngIndex As Long
        Dim lngNumber As Long
        Dim lngSize As Long
        Dim lngFile As Long
        Dim strPath As String
        Dim strFile As String
        Dim strText As String
        lngNumber = Val(InputBox("How many files do you want to create?", , "1000"))
        If lngNumber < 1 Then Exit Sub
        lngSize = Val(InputBox("What file size do you want (in bytes)?", , "1000"))
        If lngSize < 20 Then Exit Sub
        strPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"
        For lngIndex = 1 To lngNumber
            strFile = "File" & lngIndex & ".txt"
            lngFile = FreeFile
            Open strPath & strFile For Output As #lngFile
            strText = "Hello World " & lngIndex & Space(lngSize)
            strText = Left(strText, lngSize - 4)
            Write #lngFile, strText
            Close #lngFile
        Next lngIndex
    End Sub


    Regards, Hans Vogelaar

    • Marked as answer by Veerusat Tuesday, May 8, 2012 10:42 AM
    Tuesday, May 8, 2012 9:48 AM
  • Thank a lot Hans Vogelaar,

    Can You make a small update in that code so that I can say the path of the folder in which it has to create all the files.(Instead of desktop)

    And what is the maximum size that I can give for the size of the document,because when I tried with a huge number it throwed an error message saying. "Overflow"....

    Is it possible to increase the size..?

    Veeru


    • Edited by Veerusat Tuesday, May 8, 2012 12:24 PM
    Tuesday, May 8, 2012 12:14 PM
  • You can change the line

    strPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"

    to use a fixed path:

    strPath = "C:\MyFiles\Test\"

    or you can prompt the user to select a folder:

        With Application.FileDialog(4)
            If .Show Then
                strPath = .SelectedItems(1)
            Else
                MsgBox "You didn't select a folder.", vbExclamation
                Exit Sub
            End If
        End With
        If Right(strPath, 1) <> "\" Then
            strPath = strPath & "\"
        End If

    What kind of file size do you need?

    Regards, Hans Vogelaar

    Tuesday, May 8, 2012 12:44 PM
  • Yeah,,,,,,,Is this same code for prompting the user to select a folder applicable for vb6 as I see few vba code work in vb6 as well.....If not ehat change should i adopt for vb6........

    And Around..........50 to 100 GB........if not at-least 1 or 2 GB.........


    Veeru

    Thursday, May 10, 2012 3:14 PM
  • Application.FileDialog is specific to the Microsoft Office applications. You can use it from VB6 if you Automate one of the Office applications, or you can use

        strPath = CreateObject("Shell.Application").BrowseForFolder(0, "Select Folder", 0, 0)


    Regards, Hans Vogelaar

    Thursday, May 10, 2012 3:35 PM
  • The methods we've been discussing in this thread are not suitable to create such large files.

    I'm sure there are methods you can use for that, but I can't help you with it, sorry.


    Regards, Hans Vogelaar

    Thursday, May 10, 2012 3:36 PM
  • Thanks Hans Vogelaar,

    When I used the following code

    strPath = CreateObject("Shell.Application").BrowseForFolder(0, "Select Folder", 0, 0)

    Form1.Text1=strPath

    to store the path of the folder in the text box("C:/TEST?ABC").....I could only store the name of the folder in the text box("ABC").......How can this be done.


    Veeru

    Thursday, May 10, 2012 3:53 PM
  • Thats okay thanks a lot Hans Vogelaar.......

    Veeru

    Thursday, May 10, 2012 3:53 PM
  • Try this:

    strPath = CreateObject("Shell.Application").BrowseForFolder(0, "Select Folder", 0, 0).Self.Path


    Regards, Hans Vogelaar

    Thursday, May 10, 2012 4:10 PM
  • Cool.............Thanks a lot.........

    Veeru

    Thursday, May 10, 2012 4:15 PM