Open an EXCEL file for FILEOUTPUT? Can it be done?

Posted by Chris on November 26, 2001 8:52 AM

I have a master spread sheet that is opened everyday, but I would like a macro in the master spread sheet to open another NEW EXCEL SHEET as OUTPUT, kind of like when you would open a TEXT FILE like:

Open "newsheet.xls" For Output As gFileNum
Write #gFileNum, MyString, MyNumber
Close #gFileNum

Can I use these commands anyway to do this:

EXAMPLE:

I want to open another NEW SHEET as "NEWSHEET.XLS"
then I want to write values in ROW 1, COLUMNS 1-6.

Then I want to CLOSE this file "NEWSHEET.XLS"

Can this be done somehow?

Thanks,
Chris

Posted by Russell Hauf on November 26, 2001 11:11 AM

Try something like this. There are several ways to do this, and I've included some different examples of how to select cells (range & cells).

Hope this helps,

Russell

'***************************************
Sub test()

Dim xlbNew As Workbook
Dim xlbCurr As Workbook

Set xlbCurr = ActiveWorkbook
Set xlbNew = Workbooks.Add

xlbNew.SaveAs "d:\NewBook.xls"
xlbNew.ActiveSheet.Cells(1, 1).Select

xlbCurr.ActiveSheet.Range("A1:F1").Copy

xlbNew.ActiveSheet.Paste
xlbNew.ActiveSheet.Cells(1, 1).Select
xlbNew.Save
xlbNew.Close False ' Closes the workbook w/o saving; we already saved.

End Sub
'***************************************

Posted by Chris on November 27, 2001 3:58 AM

Seems like this would work very good. But it keeps two spreadsheets open at once. I think for what I need to do, this won't be a problem. Thanks for you help and direction!

Chris

Set xlbCurr = ActiveWorkbook Set xlbNew = Workbooks.Add xlbNew.SaveAs "d:\NewBook.xls" xlbNew.ActiveSheet.Cells(1, 1).Select xlbCurr.ActiveSheet.Range("A1:F1").Copy xlbNew.ActiveSheet.Paste xlbNew.ActiveSheet.Cells(1, 1).Select xlbNew.Save xlbNew.Close False ' Closes the workbook w/o saving; we already saved.