Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I tried using the code below for the Excel program on my `Mac Mini using the OS X Version 10.7.2 and it keeps saying Error due to file name / path: (The Excel file I am creating is going to be a template with my formulas and macros installed which will be used over and over).

Sub CreateFile()
Do While Not IsEmpty(ActiveCell.Offset(0, 1))
    MyFile = ActiveCell.Value & ".txt"
    fnum = FreeFile()
    Open MyFile For Output As fnum
    Print #fnum, ActiveCell.Offset(0, 1) & " " & ActiveCell.Offset(0, 2)
Close #fnum
ActiveCell.Offset(1, 0).Select
Loop
End Sub

What Im trying to do:

1st Objective

  1. I would like to have the following data to be used to create a text file. A:A is what I need the name of the file to be. B:2 is the content I need in the text file.

    So, A2 - "repair-video-game-Glassboro-NJ-08028.txt" is the file name and B2 to be the content in the file. Next, A3 is the file name and B3 is the content for the file, etc. ONCE the content reads what is in cell A16 and B16 (length will vary), the file creation should stop, if not then I can delete the additional files created.

  2. This sheet will never change. Is there a way to establish the excel macro to always go to this sheet instead of have to select it with the mouse to identify the starting point?

2nd Objective

  1. I would like to have the following data to be used to create a text file. A:1 is what I need the name of the file to be. B:B is the content I want in the file.

    So, A2 - is the file name "geo-sitemap.xml" and B:B to be the content in the file (ignore the .xml file extension in the photo). ONCE the content cell reads what is in cell "B16" (length will vary), the file creation should stop, if not then I can adjust the cells that have need content (formulated content you see in the image is preset for 500 rows).

  2. This sheet will never change. Is there a way to establish the excel macro to always go to this sheet instead of have to select it with the mouse to identify the starting point?

I can Provide the content in the cells that are filled in by excel formulas that are not not to be included in the .txt files. It is ok if it is not possible. I can delete the extra cells that are not populated (based on the data sheet).

Please let me know if you need any more additional information or clarity and I will be happy to provide it.

share|improve this question
    
The macro displays an error "Open MyFile For Output As fnum" with the following section when I try to use the code above. Do I need to set up the DIM to identify the elements and add a "myPath" line of code? –  Beginner_programmer Jul 3 '12 at 19:39
    
SOOOOOOOO ..... I never got help with my question but I was able to figure out the solution out myself! The issue was in the length of the file name. The file name with the extension (Extension in or our of the cell) is only 32 Charters. –  Beginner_programmer Aug 16 '12 at 3:14

1 Answer 1

up vote 1 down vote accepted

So here is the code I am using. It is set up to run from any page (as in you dont have to be on the page, set page in the code)

Sub CreateFile_KML()

Dim shtKML As Worksheet

Set shtKML = ThisWorkbook.Sheets("KML")

i = 2

    Do While Not IsEmpty(shtKML.Cells(i, 2))
        MyFile = "Macintosh HD:Users:cherryredspyder:Desktop:Testting:" & shtKML.Cells(i, 1).Value
        fnum = FreeFile
        Open MyFile For Output As fnum
        Print #fnum, shtKML.Cells(i, 2).Value
        Close fnum
        i = i + 1
    Loop

End Sub

The length of the file not not be longer than 32 Characters.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.