Create text file named after a cell containing other cell data

Question

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.

Answer 1

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.