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]

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).

Thanks Sunjinsak - the folder does exist on my desktop and it's called CustomMetadataImport

 

[Sunjinsak]

Sorry, I think I misread your previous post. So now you're getting runtime error 5 (invalid procedure call)? Is that right? Or do you mean you're still getting the runtime error 76 (path not found) on that line?

 

[Sunjinsak]

This is the path: filePath = CreateObject("WScript.Shell").specialFolders("Desktop") & "C:\Users\alex.jones\OneDrive\Desktop\CustomMetadataImport\" & fileName & ".md"

I know this thread is over a week old now, but I've been on holiday so only just checking back in. Seen as you haven't replied I'm assuming you've already spotted and solved this, but just in case you haven't - or for the benefit of anyone else that might find this thread one day - your issue is in the above line.

What you're doing there is appending the full, hard-coded file path to the first part of the file path. I didn't spot it at first but on re-reading your replies just now I noticed it. The "CreateObject("WScript.Shell").specialFolders("Desktop")" line will return the path to your desktop, so you only need to append the remaining path from there. It should look like this:

filePath = CreateObject("WScript.Shell").specialFolders("Desktop") & "\CustomMetadataImport\" & fileName & ".md"

Which was the code I originally gave you. Did you even try it?

Anyway, hope that made sense and now fixes your issue, if you even ever read this!