This script does everything I need but one thing: it only exports Col B, and I need it to export col B:H (using A as the filename, which this script does).
EDIT: actually, I'm not sure now if I could use this method. I looked more closely at the resulting md file and there are extra " marks all over the place, e.g., at the beginning and end of the file, as well as around all legit quotation marks from the content of the excel cells. END EDIT.
Sub DataDump()
Dim X
Dim lngRow As Long
Dim StrFolder As String
StrFolder = "C:\temp"
X = Range([a1], Cells(Rows.Count, 2).End(xlUp))
For lngRow = 1 To UBound(X)
Open StrFolder & "\" & X(lngRow, 1) & ".md" For Output As #1
Write #1, X(lngRow, 2)
Close #1
Next
End Sub
Purpose:
I need to export each row as an md file, where Col A becomes the Title and the content is Cols B:H. I can do it in google sheets, but as the source is in excel I'd prefer to use VBA. This is the first successful (and super-fast) script I've found (I have 7,000 rows).
What I've tried:
I've tried modifying the range, and I've increased all the "2"s to "5" thinking it was a column thing, but it isn't; selecting 5 just means it uses that single column.
I rarely use excel, so I really don't know my way around VBA, but I do have this google sheets script that does the trick (after I copy/paste 100 rows at a time from excel to gsheets), but man it is slow, and I can only do 100 rows at a time. Here's the google sheets script if it helps:
function saveRowsToMDfile() {
var ss = SpreadsheetApp.getActive();
var sheet = ss.getActiveSheet();
var range = sheet.getRange("A1:H100");
var rows = range.getValues();
var folder = DriveApp.getFoldersByName("Test").next();
var files = folder.getFiles();
while(files.hasNext()) files.next().setTrashed(true);
rows.forEach(function(row) {
var title = row[0]; //set first element of array as title
row.shift(); //remove first element of the array
var content = row.join("\n");
folder.createFile(title + ".md", content);
});
}
For anyone else who might be asking the same thing, the following script saves rows A-H as md files, where col A is the filename (you can modify the number of columns and change md to txt and modify the path where the file is saved) as UTF8 without the BOM. I'm not familiar enough with VB to know what can be deleted from this script - all I know is that it works like charm:
Sub DataDump()
Const STR_FOLDER As String = "D:\FilmDatabase\"
Dim ws As Worksheet, lngRow As Long, arr
Set ws = ActiveSheet
For lngRow = 1 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'get row as 2D array and convert to 1D array
arr = Application.Transpose(Application.Transpose(ws.Cells(lngRow, "B").Resize(1, 7).Value))
PutContent3 STR_FOLDER & ws.Cells(lngRow, 1) & ".md", Join(arr, vbLf)
Next lngRow
End Sub
'EDIT: try this - may handle accented text better
'https://stackoverflow.com/questions/2524703/save-text-file-utf-8-encoded-with-vba
Sub PutContent2(f As String, content As String)
With CreateObject("ADODB.Stream")
.Type = 2 'text/string data
.Charset = "utf-8"
.Open
.WriteText content
.SaveToFile f, 2 'Save to disk
End With
End Sub
Sub PutContent3(f As String, content As String)
Dim BinaryStream As Object
Dim UTFStream As Object
Set UTFStream = CreateObject("adodb.stream")
UTFStream.Type = 2
UTFStream.Mode = 3
UTFStream.Charset = "UTF-8"
UTFStream.Open
UTFStream.WriteText content
UTFStream.Position = 3 'skip BOM
Set BinaryStream = CreateObject("adodb.stream")
BinaryStream.Type = 1
BinaryStream.Mode = 3
BinaryStream.Open
UTFStream.CopyTo BinaryStream
UTFStream.Flush
UTFStream.Close
Set UTFStream = Nothing
BinaryStream.SaveToFile f, 2
BinaryStream.Flush
BinaryStream.Close
Set BinaryStream = Nothing
End Sub
