PDA

View Full Version : insert value into A1



ironj32
03-12-2007, 12:47 PM
Had this in a previous thread but didn't get the answer I needed. The following code creates a new copy of an excel file and changes the name. I also need to insert Me.txtVendorName into A1 of "Saved" worksheet of the newely created workbook.

Sub CreateSurvey()
Dim xlWB As Object
Dim xlWS As Object
Dim lRow As Long
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
Set xlWB = CreateObject("Scripting.FileSystemObject")
xlWB.copyfile "D:\Documents and Settings\JLFOGEL\Desktop\Copy of CREVendorMgmtForm1.xls", _
"D:\Documents and Settings\JLFOGEL\Desktop\" & Me.txtVendorName & ".xls"
End Sub


Edited 13-Mar-07 by geekgirlau. Reason: insert line breaks

Bob Phillips
03-12-2007, 01:10 PM
Just opne the new book, write it, save and close



Const WB_PATH As String = "D:\Documents and Settings\JLFOGEL\Desktop\"
Dim xlWB As Object
Dim xlWS As Object
Dim lRow As Long
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
Set xlWB = CreateObject("Scripting.FileSystemObject")
xlWB.copyfile WB_PATH & "Copy of CREVendorMgmtForm1.xls", _
WB_PATH & Me.txtVendorName & ".xls"
Workbooks.Open WB_PATH & Me.txtVendorName & ".xls"
Worksheets("Saved").Range("A1").Value = Me.txtVendorName
With ActiveWorkbook
.Save
.Close
End With

ironj32
03-12-2007, 01:17 PM
good call.