create temporary excel workbook with 1 sheet

I have a spreadsheet that opens a word document in vba and then merges the data on sheet4 to the word document.  Works great.  Then I move it to a SharePoint server and it keeps coming back and saying it cant find the file, with the data, which is the file that is open already open.  Again runs fine locally.  So I'm thinking because its actually in a database and not in a file is the reason I have the problem, so I'm thinking create a local temporary workbook with the data sheet in it and merge that file instead.  So the question becomes how to create the workbook, then add and name the sheet, then run my program to save data to the sheet ( this I have) and then save to the users documents folder, so that I can use that file to merge instead.

thanks for the help
mgmhicksAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
It's straightforward to create a workbook with one worksheet.

This code, run in Excel VBA, will do it.
Dim wbNew As Workbook

   Set wbNew = Workbooks.Add(xlWBATWorksheet)

   wbNew.Sheets(1) = "NewSheetName"

    ' run your code with newly created workbook

Open in new window

If you want to name the new workbook you'll need to save it.

This will save the workbook create with the previous code.
wbNew.SaveAs "C:\Test\MyFolder\NewWorksheetName.xlsx", Format:=xlOpenXMLWorkbook

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.