VBA to generate .md (format) field based on two columns in excel

[alirezaEsfand]

Hi everyone - I'm importing custom metadata into Salesforce using Excel and I need the below if possible via VBA:

VBA to allow me:
- Generate up to 500 individual files in a folder on my computer
- Name of the files to be based on what is in column A (starting with column A2)
- The content of each file to based on what is in column B (starting with column B2)
- The format of the files to be .md

i.e. A2= Name, B2= Germany ------> A file to be created called Name.md which contains Germany

 

[alirezaEsfand]

Hi all - please let me know if the above makes sense - I need the files exported to maintain the accents and special characters hence the below line (highlighted) -

Sub GenerateMDFiles()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim filePath As String
    Dim fileName As String
    Dim fileContent As String
    Dim i As Long
    Dim fileSystemObject As Object
    Dim fileStream As Object
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change Sheet1 to the name of your sheet
    
    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Create a FileSystemObject
    Set fileSystemObject = CreateObject("Scripting.FileSystemObject")
    
    ' Loop through each row
    For i = 2 To Application.WorksheetFunction.Min(501, lastRow)
        ' Get the file name and content from columns A and B
        fileName = ws.Cells(i, 1).Value
        fileContent = ws.Cells(i, 2).Value
        
        ' Define the file path
        filePath = "C:\Desktop\CustomMetadataImport\" & fileName & ".md" ' Change to your target folder path
        
        ' Create and write to the file
[B]        Set fileStream = fileSystemObject.CreateTextFile(filePath, True, True) ' True for Unicode (special characters)[/B]
        fileStream.Write fileContent
        fileStream.Close
    Next i
    
    ' Clean up
    Set fileSystemObject = Nothing
    Set fileStream = Nothing
    
    MsgBox "Files generated successfully!", vbInformation
End Sub

 

[alirezaEsfand]

Can someone help please I'm on a deadline

 

[Sunjinsak]

I'm not sure what you need help with as you seem to have solved it yourself. What part of your code isn't working? If it helps, I've made a small amendment to how you're creating your file path, and I've changed the For Loop to reference lastRow, seen as you've gone to the trouble of declaring it further up. I've tested it and it seems to work fine for me, though I'm obviously just using made-up data for the test so it might not actually reflect your use case.

Sub GenerateMDFiles()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim filePath As String
    Dim fileName As String
    Dim fileContent As String
    Dim i As Long
    Dim fileSystemObject As Object
    Dim fileStream As Object
   
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change Sheet1 to the name of your sheet
   
    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
   
    ' Create a FileSystemObject
    Set fileSystemObject = CreateObject("Scripting.FileSystemObject")
   
    ' Loop through each row
    For i = 2 To lastRow
        ' Get the file name and content from columns A and B
        fileName = ws.Cells(i, 1).Value
        fileContent = ws.Cells(i, 2).Value
       
        ' Define the file path
        filePath = CreateObject("WScript.Shell").specialFolders("Desktop") & "\CustomMetadataImport\" & fileName & ".md" ' Change to your target folder path
       
        ' Create and write to the file
        Set fileStream = fileSystemObject.CreateTextFile(filePath, True, True)
        fileStream.Write fileContent
        fileStream.Close
    Next i
   
    ' Clean up
    Set fileSystemObject = Nothing
    Set fileStream = Nothing
   
    MsgBox "Files generated successfully!", vbInformation
End Sub

 

[alirezaEsfand]

Thanks @Sunjinsak - I'm getting an error:

Run-time error '76' page not found and when debugging it go to this line: Set fileStream = fileSystemObject.CreateTextFile(filePath, True, True)

I'm not sure what you need help with as you seem to have solved it yourself. What part of your code isn't working? If it helps, I've made a small amendment to how you're creating your file path, and I've changed the For Loop to reference lastRow, seen as you've gone to the trouble of declaring it further up. I've tested it and it seems to work fine for me, though I'm obviously just using made-up data for the test so it might not actually reflect your use case.

Sub GenerateMDFiles()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim filePath As String
    Dim fileName As String
    Dim fileContent As String
    Dim i As Long
    Dim fileSystemObject As Object
    Dim fileStream As Object
  
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change Sheet1 to the name of your sheet
  
    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
  
    ' Create a FileSystemObject
    Set fileSystemObject = CreateObject("Scripting.FileSystemObject")
  
    ' Loop through each row
    For i = 2 To lastRow
        ' Get the file name and content from columns A and B
        fileName = ws.Cells(i, 1).Value
        fileContent = ws.Cells(i, 2).Value
      
        ' Define the file path
        filePath = CreateObject("WScript.Shell").specialFolders("Desktop") & "\CustomMetadataImport\" & fileName & ".md" ' Change to your target folder path
      
        ' Create and write to the file
        Set fileStream = fileSystemObject.CreateTextFile(filePath, True, True)
        fileStream.Write fileContent
        fileStream.Close
    Next i
  
    ' Clean up
    Set fileSystemObject = Nothing
    Set fileStream = Nothing
  
    MsgBox "Files generated successfully!", vbInformation
End Sub

 

[Sunjinsak]

That's most likely because the folder you're trying to refer to with filePath doesn't exist. Does that error occur when using your version of the code, or mine (or both)?

 

[alirezaEsfand]

That's most likely because the folder you're trying to refer to with filePath doesn't exist. Does that error occur when using your version of the code, or mine (or both)?

On both I'm getting the same error - This is the path: filePath = CreateObject("WScript.Shell").specialFolders("Desktop") & "C:\Users\alex.jones\OneDrive\Desktop\CustomMetadataImport\" & fileName & ".md" ' Change to your target folder path

Could you help me fix that please?

 

[Sunjinsak]

Does the folder CustomMetadataImport exist on the desktop?

Try doing:

MsgBox CreateObject("WScript.Shell").specialFolders("Desktop")

Does that return the same error?

 

[alirezaEsfand]

Does the folder CustomMetadataImport exist on the desktop?

Try doing:

MsgBox CreateObject("WScript.Shell").specialFolders("Desktop")

Does that return the same error?

I get the runtime error 5 with the below:

 

[Sunjinsak]

I know, but that will happen if the folder you're referring to with filePath doesn't already exist, hence why I asked if the folder called CustomMetadataImport exists on the desktop? If it doesn't you need to create it before the code will work. If it does exist, check if you have a typo in the folder name - it needs to match the hard-coded string in your code exactly (though it's not case sensitive).