New to Excel 2010, Macros and VBA

C

circledchicken

Well-known Member

Joined

Aug 13, 2011

Messages

2,932

• May 31, 2012

• #2

Hi and welcome to the board,

This is not a direct answer to your question but...why do you want to save the data associated with each individual trip in a separate file? Given that your goal is to forecast potential earnings, it would probably be much easier to have all the data for each trip in a single file from which you can aggregate and extract the forecasts you need.

Perhaps post some more detail including a sample of your data and expected outcomes and someone might be able to help further.

 

Upvote 0

lmvk99

Board Regular

Joined

Dec 15, 2011

Messages

68

• May 31, 2012

• #3

In C14 put =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1) which will collect the file location of the template you are using.
Then in D14 put ="EstPay"&B14&".xls" which will make the new file name. Then for your macro all you need is

Sub Macro1()
Dim pathname As String
Dim filename As String

tripno = Worksheets("Sheet1").Cells(14, 4)
pathname = Worksheets("Sheet1").Cells(14, 3)

ActiveWorkbook.SaveAs filename:=pathname + filename

End Sub

Good luck!

 

Upvote 0

ThunderGod

New Member

Joined

May 31, 2012

Messages

7

• May 31, 2012

• #4

@circledchicken

This is done on a weekly basis. pay is based on Cents Per Mile for miles driven daily. 1 trip number for the week. This will give the driver a snapshot of his/her Estimated take home pay for the week after expenses.

@lmvk99

here is the code I am trying to use, but still cannot get it to put the new file name in the input line


Sub cmd_SaveToC_Drive()


Dim strPath As String
Dim strFolderPath As String
Dim fileSaveName As Variant

strFolderPath = "C:\Users\Thor\Desktop\"
ThisFile = Range("B8").Value & ".xls"

fileSaveName = Application.GetSaveAsFilename(InitialFileName:=strFolderPath & ThisFile)
If fileSaveName = False Then Exit Sub
ThisWorkbook.SaveAs Filename:=fileSaveName

End Sub

 

Last edited: May 31, 2012

Upvote 0

ThunderGod

New Member

Joined

May 31, 2012

Messages

7

• May 31, 2012

• #5

Here is what part of the Work Sheet looks like

 

Upvote 0

lmvk99

Board Regular

Joined

Dec 15, 2011

Messages

68

• May 31, 2012

• #6

Sorry thunder god, I'm no longer at a computer where I can play with and test you code, but if you want to try mine again, I realized I made an error. It should be:


Sub Macro1()
Dim pathname As String
Dim filename As String

filename = Worksheets("Sheet1").Cells(8,2)
pathname ="C:\Users\Thor\Desktop\"

ActiveWorkbook.SaveAs filename:=pathname + "EstPay"+filename+".xls"

End Sub

 

Upvote 0

ThunderGod

New Member

Joined

May 31, 2012

Messages

7

• May 31, 2012

• #7

The code work with a modification, but get an error about saying saving as a macro enabled file which I don't want. I actually would like to (in the template) input the info and click on Save As and the File name EstPay_#####.xls would appear in the dialog box. And of course the template when closed will lose all the input inserted which it's suppose to do.

Sub Macro1()
Dim pathname As String
Dim filename As String

filename = Worksheets("Trip1").Cells(8, 2)
pathname = "C:\Users\Thor\Desktop\"

ActiveWorkbook.SaveAs filename:=pathname + "EstPay_" + filename + ".xls"

End Sub

 

Upvote 0

C

circledchicken

Well-known Member

Joined

Aug 13, 2011

Messages

2,932

• Jun 2, 2012

• #8

ThunderGod said:

@circledchicken

This is done on a weekly basis. pay is based on Cents Per Mile for miles driven daily. 1 trip number for the week. This will give the driver a snapshot of his/her Estimated take home pay for the week after expenses.

Click to expand...

Hi again,

Going back to this earlier point, I think a single spreadsheet with a flat structure will better help you accomplish your goals (although probably won't look as nice!). Something like this for the raw data:

Excel Workbook
	A	B	C	D	E	F
1	Date	Week	Date Dispatched	Empty Miles	Minimum Miles Paid
2	01/01/2012	1	02/02/2012		2500
3	08/01/2012	2	09/02/2012		2000
4
Sheet1

Excel 2010There are some best practice notes on Excel spreadsheets provided here - http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm - and one of the key points mentioned is:

Less Worksheets/Workbooks Is More And Makes Analyzing/Reporting A LOT Easier. Try And Ensure All Related Raw Data Is On 1 Worksheet And In 1 Workbook. The Number 1 Issue With Excel Spreadsheets Is Users Spreading Raw Data Over Many Worksheets And/Or Workbooks.

Click to expand...

 

Upvote 0

ThunderGod

New Member

Joined

May 31, 2012

Messages

7

• Sep 7, 2012

• #9

Here is the code I came Up with and I created a button and assigned the macro to it. And mark this Thread as SOLVED

Code:

Sub FileSave()
      
    Dim DispDate As String
    Dim PayDate As String
    
        DispDate = Sheets("TripInfo").Range("C6").Value + 12
        PayDate = Format(DispDate, "mm-dd-yyyy")
    
Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="C:\Users\Thor\Desktop\Testing Folder\" & "EstPayFor_" & PayDate, _
        Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True


End Sub

 

Upvote 0