Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro file save as, saving sheet not workbook
I am using the below routine, which I found in this newsgroup, but instead of saving the file as Postingsum011706.iif, it renames the current sheet, I don't want to change the worksheet name, I want to change the file name for Postingsum plus date, which is located in field C4. Public Sub PostingSumSave() Dim sStr As String Const sDateCell As String = "c4" Const SPath As String = "C:\access\" sStr = Format(Range(sDateCell), "mmddyy") ThisWorkbook.SaveAs Filename:=SPath & "PostingSum" & sStr & ".iif", _ FileFormat:=xlText, CreateBackup:=False If ThisWorkbook.Saved = False Then ThisWorkbook.Save Application.DisplayAlerts = True MsgBox "The Posting Summary for this week has been created, Saving and closing Workbook" ActiveWorkbook.Close End Sub I am also having problems suppressing the save messages, but I think that is related to it renaming the sheet, then of course it is asking to save the workbook. -- annep ------------------------------------------------------------------------ annep's Profile: http://www.excelforum.com/member.php...o&userid=18851 View this thread: http://www.excelforum.com/showthread...hreadid=503559 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro file save as, saving sheet not workbook
You're saving as an .iif file? What's that?
"annep" wrote in message ... I am using the below routine, which I found in this newsgroup, but instead of saving the file as Postingsum011706.iif, it renames the current sheet, I don't want to change the worksheet name, I want to change the file name for Postingsum plus date, which is located in field C4. Public Sub PostingSumSave() Dim sStr As String Const sDateCell As String = "c4" Const SPath As String = "C:\access\" sStr = Format(Range(sDateCell), "mmddyy") ThisWorkbook.SaveAs Filename:=SPath & "PostingSum" & sStr & ".iif", _ FileFormat:=xlText, CreateBackup:=False If ThisWorkbook.Saved = False Then ThisWorkbook.Save Application.DisplayAlerts = True MsgBox "The Posting Summary for this week has been created, Saving and closing Workbook" ActiveWorkbook.Close End Sub I am also having problems suppressing the save messages, but I think that is related to it renaming the sheet, then of course it is asking to save the workbook. -- annep ------------------------------------------------------------------------ annep's Profile: http://www.excelforum.com/member.php...o&userid=18851 View this thread: http://www.excelforum.com/showthread...hreadid=503559 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro file save as, saving sheet not workbook
When you save an excel workbook as a text file, you can only save that
activesheet. And one of the consequences is that worksheet gets renamed--it even happens if you do it manually. You've got a couple of choices... Save the name of the worksheet in some variable, do your SaveAs and rename the worksheet. But the problem with that is the current workbook is now the .iif (or .txt) file. You could mess up and not save the latest changes as a normal ..xls workbook. The other option is to copy that worksheet to another workbook, save that workbook as your text file and then close that workbook--the nice thing about this is that you still have that original workbook in the same state as before you saved. Option Explicit Public Sub PostingSumSave() Dim sStr As String dim Wks as worksheet Const sDateCell As String = "c4" Const SPath As String = "C:\access\" sStr = Format(Range(sDateCell), "mmddyy") activesheet.copy 'to a new workbook set wks = activesheet application.displayalerts = false wks.parent.SaveAs Filename:=SPath & "PostingSum" & sStr & ".iif", _ FileFormat:=xlText, CreateBackup:=False application.displayalerts = true wks.parent.close savechanges:=false 'do you still want/need to save the workbook? MsgBox "The Posting Summary for this week has been created" & vblf & _ "Saving and closing Workbook" End Sub I didn't test this, but it compiled ok. annep wrote: I am using the below routine, which I found in this newsgroup, but instead of saving the file as Postingsum011706.iif, it renames the current sheet, I don't want to change the worksheet name, I want to change the file name for Postingsum plus date, which is located in field C4. Public Sub PostingSumSave() Dim sStr As String Const sDateCell As String = "c4" Const SPath As String = "C:\access\" sStr = Format(Range(sDateCell), "mmddyy") ThisWorkbook.SaveAs Filename:=SPath & "PostingSum" & sStr & ".iif", _ FileFormat:=xlText, CreateBackup:=False If ThisWorkbook.Saved = False Then ThisWorkbook.Save Application.DisplayAlerts = True MsgBox "The Posting Summary for this week has been created, Saving and closing Workbook" ActiveWorkbook.Close End Sub I am also having problems suppressing the save messages, but I think that is related to it renaming the sheet, then of course it is asking to save the workbook. -- annep ------------------------------------------------------------------------ annep's Profile: http://www.excelforum.com/member.php...o&userid=18851 View this thread: http://www.excelforum.com/showthread...hreadid=503559 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro file save as, saving sheet not workbook
Dave,
Where I record a macro while copying 2 sheets in a new workbook ,converting the formula to values & saving as a new file I get a macro in the new file call "Record1" & I can't delete it - Is it a bug or harmful for the new file?? Thxs |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro file save as, saving sheet not workbook
It's probably not harmful -- but it could cause trouble for the person opening
the file. They might be confused about the "this file contains macros" warning or even worse, they could run it. But I've never seen a recorded macro get named Record1 -- unless I changed the name. Mine are named Macro1, Macro2, ... Are you recording a macro in that new workbook? If you are, then you could always store the recorded macro in a different workbook. And if your worksheets have code behind them (say for events or controls), then that code will get copied with the worksheet. I'd be very hesitant to call it a bug. Most bugs I see are just my errors. al007 wrote: Dave, Where I record a macro while copying 2 sheets in a new workbook ,converting the formula to values & saving as a new file I get a macro in the new file call "Record1" & I can't delete it - Is it a bug or harmful for the new file?? Thxs -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro file save as, saving sheet not workbook
".iif" is actually a tab delimiter text file. This file is for import into Quickbooks. I could use the same file and make it a ".txt", it just one more step when selecting the import into Quickbooks. Previously I used this routine without the addition of the date and if worked fine: Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:="C:\Access\PayrollPosting.iif", FileFormat _ :=xlText, CreateBackup:=False If ThisWorkbook.Saved = False Then ThisWorkbook.Save ActiveWorkbook.Close Application.DisplayAlerts = True Thanks, Anne -- annep ------------------------------------------------------------------------ annep's Profile: http://www.excelforum.com/member.php...o&userid=18851 View this thread: http://www.excelforum.com/showthread...hreadid=503559 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro file save as, saving sheet not workbook
Dave, I think I skipped some important info. The data comes from Access. I run an event in Access, which transfer data from Access to Excel. Access opens the Postingsum.xlt and copies the data from an access query into the xlt file. The user then runs the macro which converts the data to a format that Quickbooks accepts. It then saves the file as the tab delimter text file using the .iif extension. The xlt never gets changed. I tried your macro and if works well, but it does not close the template. Of course even nicer would be, if the macro could be invoked directly from Access or run an automacro. Just have not figured out how to do that, since it cannot be in the on open event of the xlt file since the copying of the data has to take place first. Anne -- annep ------------------------------------------------------------------------ annep's Profile: http://www.excelforum.com/member.php...o&userid=18851 View this thread: http://www.excelforum.com/showthread...hreadid=503559 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro file save as, saving sheet not workbook
Dave, I have your macro saving the iif.file. I then had to add Close the xlt file. I just can't get it to close excel. I added the following lines ActiveWorkbook.Close Application.Quit the close works, but the quit does not. Anne -- annep ------------------------------------------------------------------------ annep's Profile: http://www.excelforum.com/member.php...o&userid=18851 View this thread: http://www.excelforum.com/showthread...hreadid=503559 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro file save as, saving sheet not workbook
Do you really open the .xlt file or do you create new workbook based on that
..xlt template? dim newwkbk as workbook set newwkbk = workbooks.add(template:="c:\somefolder\Postingsum. xlt") 'do the work 'and close the .iif file newwkbk.close savechanges:=false set newwkbk=nothing 'set allyourotherobjects = nothing application.quit ====== If you're opening excel, then ok. But if you're using an already running instance of excel, I'd be very hesitant to close the application. annep wrote: Dave, I have your macro saving the iif.file. I then had to add Close the xlt file. I just can't get it to close excel. I added the following lines ActiveWorkbook.Close Application.Quit the close works, but the quit does not. Anne -- annep ------------------------------------------------------------------------ annep's Profile: http://www.excelforum.com/member.php...o&userid=18851 View this thread: http://www.excelforum.com/showthread...hreadid=503559 -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro file save as, saving sheet not workbook
I am using Lou Kapeller application, published on the UtterAcces newgroup. Private Sub cmdTransferDataToExcel_Click() On Error Resume Next Dim db As Database Dim rst As Recordset Dim objApp As Excel.Application Dim objBook As Excel.Workbook Dim objSheet As Excel.Worksheet Dim Path As String Set db = CurrentDb() Set objBook Workbooks.Add(Template:="C:\Access\PostingSum.xlt" ) 'Your exce spreadsheet file goes here Set objApp = objBook.Parent Set objSheet = objBook.Worksheets("Sheet1") 'Nam of sheet you want to export to objBook.Windows(1).Visible = True Set rst db.OpenRecordset("QryPostingSumPayrollFinal") 'Opens the recordset an sets the variable With objSheet .Select .Range("a2:h500").Clear 'Clears the current data i the workbook range .Range("A2:h2").CopyFromRecordset rst 'rst Copies th recordset into the worksheet End With rst.Close objApp.Visible = True Set rst = Nothing Set db = Nothing Set objSheet = Nothing Set objBook = Nothing Set objApp = Nothing End Sub When this is finished I have the PostingSum.xlt open in Excel. Then the user has to initiate the macro which formats the spreadshee and the macro then saves the new data to the postinsum.iif. I thought that when I do a "save as", it closes the xlt and it leave open the new textfile. It also behaved like that. My original macro without the date was as follows, it never changed th xlt, created the iif file and displayed the message, and after the use clicked ok on the message box, excel closed: ... Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:="C:\Access\Postingsum.iif" FileFormat _ :=xlText, CreateBackup:=False If ThisWorkbook.Saved = False Then ThisWorkbook.Save Application.DisplayAlerts = True MsgBox "The Posting Summary for this week has been created, Savin and closing Workbook" ActiveWorkbook.Close End If End Sub Ann -- anne ----------------------------------------------------------------------- annep's Profile: http://www.excelforum.com/member.php...fo&userid=1885 View this thread: http://www.excelforum.com/showthread.php?threadid=50355 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro file save as, saving sheet not workbook
After reading what you're doing, it sounds like you could drop all that stuff
that I suggested. After you save the file (as .IIF), you can just close the file and not worry about the template. Does that second snippet of code exists in the template? If it does, then I think that this will work. If it doesn't exist in that template, then I would think it would make much more sense there. (In fact, I'd put a button from the Forms Toolbar on Sheet1 of the template. When you save the file as a text file, then the button won't come with it--and neither will the code. It checks to see how many workbooks are open (including hidden workbooks). If it sees more than one, then it just closes that workbook. If it only sees one, it closes the application. In both cases, the file was already saved as .IIF.) Sub doTheWork() Dim WorkbookCtr As Long Dim wCtr As Long Dim wkbk As Workbook Application.DisplayAlerts = False ThisWorkbook.SaveAs Filename:="C:\Access\Postingsum.iif", _ FileFormat:=xlText, CreateBackup:=False Application.displayalerts = true MsgBox "The Posting Summary for this week has been created, " & _ "Saving and closing Workbook" If Workbooks.Count 1 Then ThisWorkbook.Close savechanges:=False Else Application.DisplayAlerts = False Application.Quit End If End Sub I know nothing about access, but this is a way you can check to see if there is already an instance of excel running--then you can use that instance to manipulate the data. (It compiled ok--after a couple of de-Accessifying line changes, but I didn't test it.) Option Explicit Private Sub cmdTransferDataToExcel_Click() Dim db As Database Dim rst As Recordset Dim objApp As Excel.Application Dim objBook As Excel.Workbook Dim objSheet As Excel.Worksheet Dim Path As String Dim XLWasRunning As Boolean XLWasRunning = True On Error Resume Next Set objApp = GetObject(, "Excel.Application") If Err.Number < 0 Then Set objApp = CreateObject("Excel.Application") XLWasRunning = False End If On Error GoTo 0 objApp.Visible = True Set db = CurrentDb() Set objBook = objApp.Workbooks.Add(Template:="C:\Access\PostingS um.xlt") Set objSheet = objBook.Worksheets("Sheet1") Set rst = db.OpenRecordset("QryPostingSumPayrollFinal") objSheet.Range("a2:h500").Clear objSheet.Range("A2:h2").CopyFromRecordset rst rst.Close Set rst = Nothing Set db = Nothing Set objSheet = Nothing Set objBook = Nothing Set objApp = Nothing End Sub annep wrote: I am using Lou Kapeller application, published on the UtterAccess newgroup. Private Sub cmdTransferDataToExcel_Click() On Error Resume Next Dim db As Database Dim rst As Recordset Dim objApp As Excel.Application Dim objBook As Excel.Workbook Dim objSheet As Excel.Worksheet Dim Path As String Set db = CurrentDb() Set objBook = Workbooks.Add(Template:="C:\Access\PostingSum.xlt" ) 'Your excel spreadsheet file goes here Set objApp = objBook.Parent Set objSheet = objBook.Worksheets("Sheet1") 'Name of sheet you want to export to objBook.Windows(1).Visible = True Set rst = db.OpenRecordset("QryPostingSumPayrollFinal") 'Opens the recordset and sets the variable With objSheet Select Range("a2:h500").Clear 'Clears the current data in the workbook range Range("A2:h2").CopyFromRecordset rst 'rst Copies the recordset into the worksheet End With rst.Close objApp.Visible = True Set rst = Nothing Set db = Nothing Set objSheet = Nothing Set objBook = Nothing Set objApp = Nothing End Sub When this is finished I have the PostingSum.xlt open in Excel. Then the user has to initiate the macro which formats the spreadsheet and the macro then saves the new data to the postinsum.iif. I thought that when I do a "save as", it closes the xlt and it leaves open the new textfile. It also behaved like that. My original macro without the date was as follows, it never changed the xlt, created the iif file and displayed the message, and after the user clicked ok on the message box, excel closed: .. Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:="C:\Access\Postingsum.iif", FileFormat _ :=xlText, CreateBackup:=False If ThisWorkbook.Saved = False Then ThisWorkbook.Save Application.DisplayAlerts = True MsgBox "The Posting Summary for this week has been created, Saving and closing Workbook" ActiveWorkbook.Close End If End Sub Anne -- annep ------------------------------------------------------------------------ annep's Profile: http://www.excelforum.com/member.php...o&userid=18851 View this thread: http://www.excelforum.com/showthread...hreadid=503559 -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro file save as, saving sheet not workbook
I really like the button idea, didn't even know I could do that. I am still trying to save the iif file with PostingSum & date.iif. I put your codes together, but I again changes the sheet name to PostingSum011706, but the sheet name needs to stay constant because Access looks for the sheet name. Any other ideas? Anne Sub doTheWork() Dim sStr As String Dim WorkbookCtr As Long Dim wCtr As Long Dim wkbk As Workbook Const sDateCell As String = "c4" sStr = Format(Range(sDateCell), "mmddyy") Application.DisplayAlerts = False ThisWorkbook.SaveAs Filename:="C:\Access\Postingsum" & sStr & ".iif", _ FileFormat:=xlText, CreateBackup:=False Application.DisplayAlerts = True MsgBox "The Posting Summary for this week has been created, " & _ "Saving and closing Workbook" If Workbooks.Count 1 Then ThisWorkbook.Close savechanges:=False Else Application.DisplayAlerts = False Application.Quit End If End Sub -- annep ------------------------------------------------------------------------ annep's Profile: http://www.excelforum.com/member.php...o&userid=18851 View this thread: http://www.excelforum.com/showthread...hreadid=503559 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro file save as, saving sheet not workbook
I am loosing it. It works, because the xlt file stays unchanged, (I forgot to mention, that the macro is in the template) It changes the sheet name on the iif file, which does not matter. Thanks, a lot Anne -- annep ------------------------------------------------------------------------ annep's Profile: http://www.excelforum.com/member.php...o&userid=18851 View this thread: http://www.excelforum.com/showthread...hreadid=503559 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro file save as, saving sheet not workbook
Why does Access care?
I thought that the second code formatted, saved and closed the text (.iif) file. And once you save (and close) that workbook as a text file, there isn't a worksheet anymore--it's just plain old text. == Since I'm obviously missing something basic, you could either use the copy to new workbook and save from there or just rename the sheet after you do the save. thisworkbook.saveas.... Thisworkbook.worksheets(1).name = "Sheet1" (since there's only one sheet in the template.) annep wrote: I really like the button idea, didn't even know I could do that. I am still trying to save the iif file with PostingSum & date.iif. I put your codes together, but I again changes the sheet name to PostingSum011706, but the sheet name needs to stay constant because Access looks for the sheet name. Any other ideas? Anne Sub doTheWork() Dim sStr As String Dim WorkbookCtr As Long Dim wCtr As Long Dim wkbk As Workbook Const sDateCell As String = "c4" sStr = Format(Range(sDateCell), "mmddyy") Application.DisplayAlerts = False ThisWorkbook.SaveAs Filename:="C:\Access\Postingsum" & sStr & ".iif", _ FileFormat:=xlText, CreateBackup:=False Application.DisplayAlerts = True MsgBox "The Posting Summary for this week has been created, " & _ "Saving and closing Workbook" If Workbooks.Count 1 Then ThisWorkbook.Close savechanges:=False Else Application.DisplayAlerts = False Application.Quit End If End Sub -- annep ------------------------------------------------------------------------ annep's Profile: http://www.excelforum.com/member.php...o&userid=18851 View this thread: http://www.excelforum.com/showthread...hreadid=503559 -- Dave Peterson |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro file save as, saving sheet not workbook
Please ignore my previous message. Now we're cooking with gas--and at these
rates, we better be making something good! (huh???). annep wrote: I am loosing it. It works, because the xlt file stays unchanged, (I forgot to mention, that the macro is in the template) It changes the sheet name on the iif file, which does not matter. Thanks, a lot Anne -- annep ------------------------------------------------------------------------ annep's Profile: http://www.excelforum.com/member.php...o&userid=18851 View this thread: http://www.excelforum.com/showthread...hreadid=503559 -- Dave Peterson |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro file save as, saving sheet not workbook
Yes, I am really pleased, you taught me a lot. Thanks again, Anne -- annep ------------------------------------------------------------------------ annep's Profile: http://www.excelforum.com/member.php...o&userid=18851 View this thread: http://www.excelforum.com/showthread...hreadid=503559 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Save just one sheet to new workbook. | Excel Worksheet Functions | |||
How do I run a macro upon saving a file but prior to actual save? | Excel Discussion (Misc queries) | |||
how do I get a macro to save a sheet and set the file name? | Excel Discussion (Misc queries) | |||
Macro to save excel sheet in a workbook | Excel Discussion (Misc queries) | |||
Macro to insert values from a file and save another sheet as a .txt file | Excel Programming |