Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Amend a create file vba sub

Posted on 2014-07-22
7
Medium Priority
?
221 Views
Last Modified: 2014-07-22
I have the below sub which i need amending, i need the code to recognise thats its 2014 and the month, so next month it would save the file in the folder ("\\ukxyzmdata23456\rights\debt2014\201407\", "The file")

Im not sure there is a need for the function as i have no requirement to save the file with any day difference

Many thanks

------------------------------------------

Sub CreateFile()
Dim strFile As String
strFile = Date_FileName("\\ukxyzmdata23456\rights\debt\2014\201406\", "The file ")

If Dir(strFile) <> "" Then
   If MsgBox("File already exists - overwrite?", vbYesNo) = vbYes Then
      Kill strFile
     

   Else
      Exit Sub
   End If
End If
ActiveWorkbook.SaveAs Filename:= _
strFile, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False




End Sub

Function Date_FileName(pPath As String, pFilePrefix As String) As String
Dim DayOfWeek As Integer, DayDiff As Integer, CharDate As String, MonthNo As Integer, CharYear As String, CharMonth  As String
Dim Result As String
   
    DayOfWeek = Weekday(Date)                                   ' Sunday is 1, Monday is 2, Tuesday is 3 etc
    If DayOfWeek = 2 Then                                       ' If a monday then we need the date as of friday, otherwise previous day
        DayDiff = 3
    Else
        If DayOfWeek = 1 Then
          DayDiff = 2                                           ' If a Sunday then we need the date as of friday, otherwise previous day
        Else
          DayDiff = 1                                           ' otherwise it should be set to the previous day
        End If
    End If
                 ' Get the character date formwatted how we want
    CharMonth = Format(Date - DayDiff, "m mmm yyyy")
    CharDate = Format(Date - DayDiff, "dd mmm yy")
    Result = pPath & CharMonth & "\" & pFilePrefix & "" & CharDate & ".xls"
    Date_FileName = Result
End Function
0
Comment
Question by:Seamus2626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 10

Expert Comment

by:Gašper Kamenšek
ID: 40210951
Hi,

all you need is a

yearmonth = Format(Date, "yyyymm")

and then a

strFile = Date_FileName("\\ukxyzmdata23456\rights\debt\2014\" & yearmonth & "\", "The file ")

Hope this helps
0
 

Author Comment

by:Seamus2626
ID: 40210956
Hi GasperK,

I dont mean to be lazy or stupid, but i am still relatively new to VBA, could you work that line into my code or show it as it would appear in a stand alone sub?

Thanks!
0
 
LVL 10

Expert Comment

by:Gašper Kamenšek
ID: 40210968
Sub CreateFile()
Dim strFile As String

yearmonth = Format(Date, "yyyymm")

strFile = "\\ukxyzmdata23456\rights\debt\2014\" & yearmonth & "\The file"


If Dir(strFile) <> "" Then
   If MsgBox("File already exists - overwrite?", vbYesNo) = vbYes Then
      'Kill strFile
     

   Else
      Exit Sub
   End If
End If
ActiveWorkbook.SaveAs Filename:=strFile

End Sub

Open in new window


This is basically it, the kill file and some other parts of the code were not ok, so i added a comment to that or just deleted it, but the rest should work.
Try it out...
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Seamus2626
ID: 40211017
Hi GasperK,

I have created the below sub, and am getting the message "Bad file name or number"




Sub CreateFile2()
Dim strFile As String

yearmonth = Format(Date, "yyyymm")

strFile = "\\R:\SPM\Monthly_Inputs\2014\Reporting" & yearmonth & "\The file.xlsx"


If Dir(strFile) <> "" Then
   If MsgBox("File already exists - overwrite?", vbYesNo) = vbYes Then
      'Kill strFile
     

   Else
      Exit Sub
   End If
End If
ActiveWorkbook.SaveAs Filename:=strFile

End Sub
0
 

Author Comment

by:Seamus2626
ID: 40211021
I have this sub for opening files in the same folder

FileYear = Year(Date)

MonthOffset = 0
If Day(Date) = 1 Then MonthOffset = 1
FileMonth = Month(Date) - MonthOffset
FileDate = Format(Date - 1, "yyyymm")
FilePath = "R:\SPM\Monthly_Inputs\" & FileYear & "\" & FileDate & "\Reporting\June_2014_Win_Loss_Jack.xlsx"

Workbooks.Open (FilePath)

Could you amend so it doesnt open the file, but saves as into that folder?

Thanks
0
 
LVL 10

Accepted Solution

by:
Gašper Kamenšek earned 2000 total points
ID: 40211147
You keep all declarations the same and replace the
Workbooks.Open (FilePath)
line with the
ActiveWorkbook.SaveAs Filename:=FilePath

Kind regards

PS: I think it would be wise to do a Format(Date-1,"mmmm") for the automatization of the file name "June_2014...
0
 

Author Closing Comment

by:Seamus2626
ID: 40211157
Thanks!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question