Export range of single cell values to text files

[TheChad77]

Hi folks, hope you can help please. I've seen lots of posts about exporting Excel data but not in the way I'm after. I have a spreadsheet where I want to export the value of each cell in column G to an individual text file - preferably with the text file named after the value of the corresponding cell from column C, but that's not particularly important. So for example, the value of G2 would be saved as a text file. Cells G3, G4, G5 and the other 100+ cells in column G would also need to be saved as individual text files as part of this process. Could anyone suggest the best method for doing this please? Thank you!

 

[DanteAmor]

preferably with the text file named after the value of the corresponding cell from column C

If you don't have a date in the cells of column C, try this:

Sub Create_Txt_File()
  Dim a As Variant, i As Long
  
  a = Range("C2:G" & Range("G" & Rows.Count).End(3).Row).Value2
  For i = 1 To UBound(a, 1)
    Open ThisWorkbook.Path & "\" & a(i, 1) & ".txt" For Output As #1
    Print #1, a(i, 5)
    Close #1
  Next i
End Sub

 

[TheChad77]

Hi @DanteAmor ! Thank you very much for your quick response. I've just tried this but I hit a "Run-time error '52' - Bad file name or number" message. I'll attach screenshots. Debugging points to the "Open ThisWorkbook" line, but I'm not clever enough to understand what the problem is. I have had to add more data to the spreadsheet, so what was column C is now column M, and what was column G is now column AX. I adjusted the script accordingly for that. Thank you!!

 

[DanteAmor]

Did the macro generate a file or stop at the first record?
You can check the folder where you have the file with the macro if it generated one or more files.

Please answer:
What do you have in column M ?, you can put examples.
Do you have blank cells in column M?
In column M do you have a formula?
In column M do you have merged cells?
In column M you have error cells like #NA, #Name #Value

 

[TheChad77]

Hi @DanteAmor, again, thank you for your help with this. The macro has not created any files yet. Column M just has six-digit reference numbers, no special characters, no formulas are being used in that column. The cells are not merged, there are no error cells and the format of cells in the column is "General". The first few examples are shown below. Thank you!

 

[DanteAmor]

Try this.
But don't modify anything in the macro.

Option Explicit

Sub Create_Txt_File()
  Dim a As Variant, i As Long
  
  a = Range("M2:AX" & Range("AX" & Rows.Count).End(3).Row).Value2
  For i = 1 To UBound(a, 1)
    Open ThisWorkbook.Path & "\" & a(i, 1) & ".txt" For Output As #1
    Print #1, a(i, 38)
    Close #1
  Next i
End Sub

 

[TheChad77]

@DanteAmor you, sir, are a genius! I still got the same error, but I wondered if it just didn't like the choice of file location. My spreadsheet was in my OneDrive folder. When I saved the spreadsheet to my local drive, the script ran just beautifully - I also tested the original script in the new location, and that worked just fine too. Guess it just didn't want to play with OneDrive. Thank you so much for all your help with this, I'm extremely grateful to you for your help! Best wishes to you

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[walt662]

I'm 2 years late to this haha, but is there a way to do this exact same thing, except instead of exporting the Excel cells as .txt files, is there a way to export the Excel cells as .png files? I've been looking for something like this forever, and I can't seem to figure it out myself. Help would be much appreciated! I tried changing the code where it says ".png" in place of ".txt" but it didn't seem to export the PNG files correctly. I assumed it is more complicated than just changing the ".txt" to ".png" but it was worth a shot. Let me know if there is a way to do this, it would be amazing!