View Full Version : Save as Macro
maninjapan
04-19-2011, 08:05 AM
I found the following simple macro to save as a new file. How can I add to this to specify the folder and new name to save the file as? for example save to C://TestFolder/SavehereFolder
Thanks in advance.
Public Sub SaveAsA1() ThisFile = Range("A1").Value ActiveWorkbook.SaveAs Filename:=ThisFileEnd Sub
Bob Phillips
04-19-2011, 08:16 AM
Public Sub SaveAsA1()
ThisFile = Range("A1").Value
ActiveWorkbook.SaveAs Filename:="C:\test\subtest\" & ThisFile
End Sub
maninjapan
04-19-2011, 08:45 AM
Thanks Xld, Works exactly as it should, but Ive just figured out its not what I need. I'm using RTD links to retrieve data and need to save the data at certain time periods. I guess I need the macro to copy/paste special to a new workbook or CSV file then save that worksheet in the specified folder. Obviously mroe complicated than I started with, but if there are any examples, I would be happy to take a look at them first.
Thanks
Bob Phillips
04-19-2011, 09:06 AM
Look at SaveCopyAs in VBA help.
maninjapan
04-20-2011, 09:19 AM
I was able to adapt a macro I found elsewhere on this thread. By itself it seems to work as I need it to, so I was looking to add an If statement. I need it to execute at a fixed time. I have a running clock in my sheet (K4) and I want it to execute at the time entered in cell (K5). I right clicked on the sheet tab and pasted the code through there, but it doesnt seem to be executing. Have I missed something here?
Private Sub CopySheetAll()
Dim wks As Worksheet
Dim wb As Workbook
Dim Path As String
If Range("K4").Value = Range("K5").Value Then
Set wks = ThisWorkbook.Worksheets("Data")
Set wb = Workbooks.Add(xlWorksheet)
wks.Copy After:=wb.Worksheets(1)
Application.DisplayAlerts = False
wb.Worksheets(1).Delete
Application.DisplayAlerts = True
Set wks = wb.Worksheets(1)
'unprotect or protect w/UserInterfaceOnly here
wks.UsedRange.Value = wks.UsedRange.Value
wb.SaveAs Filename:="C:\test\" & Range("K2").Value
End If
End Sub
Thanks
BrianMH
04-20-2011, 12:35 PM
You need to run it at a particular time? Check out the help for OnTime.
Unless your macro above is constantly running in a loop and recalculating it won't ever pick up that its the time you want.
maninjapan
04-21-2011, 08:43 AM
I tried the following:
pasted in This Workbook
Private Sub SaveSheet()
Application.OnTime TimeValue("00:31:00"), "CopySheetAll"
EndSub
Pasted in a module:
Private Sub CopySheetAll()
Dim wks As Worksheet
Dim wb As Workbook
Dim Path As String
Set wks = ThisWorkbook.Worksheets("Data")
Set wb = Workbooks.Add(xlWorksheet)
wks.Copy After:=wb.Worksheets(1)
Application.DisplayAlerts = False
wb.Worksheets(1).Delete
Application.DisplayAlerts = True
Set wks = wb.Worksheets(1)
'unprotect or protect w/UserInterfaceOnly here
wks.UsedRange.Value = wks.UsedRange.Value
wb.SaveAs Filename:="C:\test\" & Range("K2").Value
End Sub
I've changed the time a few times trying to get the Macro to execute, but not having any luck. (my PC clock is set to local Japanese time so time is current). What am I missing here?
Simon Lloyd
04-21-2011, 09:02 AM
Check out Chip's site for the proper formation http://www.cpearson.com/excel/OnTime.aspx
maninjapan
04-21-2011, 10:21 AM
Thanks for the tip simon, I had a look and it shows an example of a timer every N seconds or minutes, but not just at a fixed time each day.
Simon Lloyd
04-21-2011, 10:31 AM
You have to run the timer every n minutes or hours and check whether the time at runtime is >= a static time.
Straight out of VBA help files:
Dim MyTime
MyTime = #4:35:17 PM#
' Assign a time.Time = MyTime
' Set system time to MyTime.
BrianMH
04-21-2011, 10:34 AM
Private Sub Workbook_Open()
Application.OnTime TimeValue("18:15:00"), "CopySheetAll", , True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "CopySheetAll", , False
End Sub
put that in the workbook module.
and the below in a standard module
Sub CopySheetAll()
Dim wks As Worksheet
Dim wb As Workbook
Dim Path As String
Set wks = ThisWorkbook.Worksheets("Data")
Set wb = Workbooks.Add(xlWorksheet)
wks.Copy After:=wb.Worksheets(1)
Application.DisplayAlerts = False
wb.Worksheets(1).Delete
Application.DisplayAlerts = True
Set wks = wb.Worksheets(1)
'unprotect or protect w/UserInterfaceOnly here
wks.UsedRange.Value = wks.UsedRange.Value
wb.SaveAs Filename:="C:\test\" & Range("K2").Value
Application.OnTime TimeValue("18:15:00"), "CopySheetAll"
End Sub
adjust the times as necessary of course and you will either need to run your copysheetall macro once or close and reopen the workbook.
maninjapan
04-25-2011, 09:38 AM
thanks, it seems to be working some of the time, but everytime I shut down excel I get a runtime error '1004' and it highlights the following line of code.
Application.OnTime dTime, "CopySheetAll", , False
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.