VBA code to save current Excel Worksheet by another name 1

[BusMgr]

In my access form I have page that has an Excel worksheet linked to an object frame. (Works okay). One button on form calls the empty worksheet. I want a second button that saves the original worksheet but with data entered under a new name that is set in the code. I have a third button that calls the worksheet with data in it and allows for updates. I also want the second button to be able to save the updated worksheet without additional interface from the user.

My current code is as follows but it does not allow for the updated worksheet to be saved, once a file has been created with the Pipetally Sheet1.xls name:

Private Sub Command2_Click()
On Error GoTo Error_Command2_Click

Dim appExcel As Excel.Workbook

Set appExcel = GetObject(, "Excel.Workbook&quot
appExcel.SaveAs "D:\RBI Tools\Pipetally Sheet1.xls"
appExcel.Application.Quit
Set appExcel = Nothing
Exit_Command2_Click:
Exit Sub
Error_Command2_Click:
MsgBox CStr(Err) & " " & Err.Description
Resume Exit_Command2_Click
End Sub

 

[Kaulana]

Sounds like at different times this empty worksheet is going to be opened, so it will need a different name each time it's saved. For that you'll need to devise a control variable somewhere, or maybe you can simply add the day and minute to the filename so it will most likely be different (1 chance in 60 it won't work on any particular day). Anyway, that's what I see. There are numerous ways to get around it, but just in case I'm way, way off of what you're trying to accomplish, I'll save the rest of my energy. =] Kaulana (aka Bob)

http://www.bobfarley.com

 

[ratman]

I'm confused.
Please explain this so even I can understand....hehehehe

Consider:

timeraw = Now()
timeedited = Format(timeraw, "mdyyhmm&quot& ".xls"
mypath = "C:\Plan Files\"
filebody = "SHIPLOG_"

ActiveWorkbook.SaveAs FileName:=mypath & filebody & formattedrundate, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False



If I'm way off on this please email me at
drat@mediaone.net

 

[Kaulana]

I'm seeing a "template" file opened as a default but then "saved as" for one reason or another. The "save as" would have to be something slightly different each time if it were to be a unique file and not simply overwrite one that was previously opened, edited, and saved. Maybe I should just wait till the guy comes back to elucidate the matter. =| Kaulana (aka Bob)

http://www.bobfarley.com

 

[BusMgr]

The first three pages of the access application are utilized multi times on a daily basis, but are also tied to a project that may last some number of days. The Excel workbook starts out blank and has data added to it over the course of the project. I have put the third button on the form to open the updated Excel wb. What I ultimatly want is for the application to pull a unique identifier from the Access db (perhaps the primary key on the projectid table) and utilize that in the name of the updated Excel wb.

ie if the project name is shell50 (from the projectid table), do a save as on the workbook as shell50.xls.

Thanks for your help.

BusMgr

 

[Kaulana]

Piece of cake. VBA for Excel will let you build a name for your workbook and save it as such. Here's code I use in an application. (This is basically the same thing as ratman wrote above, just different.) I'm not sure how you get your Access name into the mix, but if you're writing Access, I'm sure you can figure that part out.

THISREFERENCE=Cells(RowCount,1)
'THISREFERENCE is the unique identifier which I get from an Excel sheet that adds a new number each time the form is opened

ActiveWorkbook.SaveAs FileName:= _
"C:Farm Documents\Farm Excel Documents\Work Orders\" & THISREFERENCE & ".xls", FileFormat:=xlNormal, Password:="",WriteResPassword:="",ReadOnlyRecommended:=False,CreateBackup:=False

Of course, the path listed above is unique to my application, and you can variablize it as ratman has done above.
Kaulana (aka Bob)

http://www.bobfarley.com