Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2557
  • Last Modified:

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
0
mgmhicks
Asked:
mgmhicks
1 Solution
 
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now