I got my first consulting opportunity for my excel VBA skills for a professional services firm. I've been trying to learn as much as I can so doors open, and lo and behold.

I realize I have to keep leveling up my game and more doors will open and I will have less grounds for imposer syndrome. I want to have more theoretical and practical knowledge and this question covers both.

I have the following snippet of code:
    Open "C:\VBATestFolder\GrossWagesDict.csv" For Output As #1
        For i = 0 To dict.count - 1
            Print #1, dict.Keys(i) & "," & dict.Items(i)
        Next i
    Close #1
It basically makes a csv file of two columns, a key:value pair. It works, as a csv or txt. I never tried saving it in any other form until now, as an xlsx. When I change the path to .xlsx it saves it alright, but I get this error when I open it:

"Excel cannot open the file "My File" because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the form of the file."
It opens as expected when I rename the file to .csv, because that's what it inherently is.

So I guess my questions are as follows:
  1. Does the open statement only write and read from standard text file formats (because csv is basically a text file with a different designation)? Are there any other formats it can write to?
  2. Is there a way to use the open statement or other function to write to xlsx? The big reason is I want to create a workbook with multiple worksheets for convenience instead of multiple csv workbooks.
  3. I'm researching the modes- Output vs Random vs Binary. Is there any time you would use binary or random? Also I've never specified the access but it always seems I can write. Is that because write is the default? The docs are not very clear on this point : Open Statement docs

Get better and better, learn what's important and become worth your salt.

Thanks!