inserting a value in access into an excel file name.

[ironj32]

hi people. i have an access db which has a form that i am going to use to send out emails with an attached excel survey. i was wondering if there was a way to take the value in txtVendorName and insert it into the file name of my excel survey. probably what will happen is that command button in access "cmdTest" will:

-open the excel workbook
-the value from txtVendorName will be inserted into a cell "A1"
-the workbook will then have to SaveAS with the value of cell A1 as the workbook name
-the workbook then closed
-the workbook then attached in my email code

i already have the email part working and it attaches an excel workbook. i need to figure out how to do the above though. Thanks for any help!

[JimmyTheHand]

Hi :hi:

If you don't need the vendorname in A1 then I would suggest simply copying the Excel file with a new name, and attaching the new file to the email.


Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
fs.copyfile "D:\1.xls", "D:\2.xls"


Jimmy

[OBP]

Taking Jimmy's point you can use "txtVendorName" directly in the new Excel Filename, where he shows "D:\2.xls" you can use
"D:\" & me.txtVendorName & ".xls"

[ironj32]

Thanks a ton guys! I have it so it creates the file name correctly.

Now I need it to get it to also insert the txtVendorName as the Title of the survey (on the excel form).

[ironj32]

I actually am going to need to get the txtVendorName into A1. The function that you guys gave me works perfect...I need to get it in there for other purposes. Any thoughts???

[OBP]

I have the code for opening an Excel sheet from Access and inserting data, it sometimes "locks up" the Excel file for editing but should be Ok for your use.
But there is also this KB article by Ken Puls which shows opening an Excel Sheet which you can use with a little "chopping" to get the parts that you want.
http://vbaexpress.com/kb/getarticle.php?kb_id=707

If you need my version let me know.

[ironj32]

I am trying to make this code work below, but i keep getting "object doesn't support this property or method" error at the

With xlWB.sheets("Saved") point.???


Sub CreateSurvey()
Dim xlWB As Object
Dim xlWS As Object
Dim lRow As Long
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"
With xlWB.sheets("Saved")
.range("A" & lRow) = txtVendorName
End With
End Sub

[OBP]

Where does ("Saved") come from?

[ironj32]

"Saved" comes from:
"D:\Documents and Settings\JLFOGEL\Desktop\Copy of CREVendorMgmtForm1.xls"

[ironj32]

Would anyone happen to have any answers for me?

[OBP]

ironj32, it could be that the code that you have used -


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"

Does not actually leave the workbook in an "Open" state for you to work with.

Try using another set statement to actually open the new workbook that you have just createdv i.e.


Set Wkb = AppExcel.Workbooks.Open("D:\Documents and Settings\JLFOGEL\Desktop\" & Me.txtVendorName & ".xls" )