Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 21st 17, 03:24 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 42
Default list 2 cell values in saveas filename

Hello

I am trying to use VBA to save a file and want two cell values in the new filename. One is a text value in cell B2, the other is a date in cell B1. I have tried this many ways but cannot get it to work. Below is a modified example that I found on the net:

Sub filesave()
Dim Path As String
Dim Rep As String
Dim RDate As Date
Path = "C:\MyFiles\"
Rep = Range("B2").Value
RDate = Range("B1").Value
ActiveWorkbook.SaveAs filename:=Path & Rep & RDate & ".xls", _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub


Please let me know what is causing this code to error out.

Thanks

Scott

  #2   Report Post  
Old April 21st 17, 08:29 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 758
Default list 2 cell values in saveas filename

Hello

I am trying to use VBA to save a file and want two cell values in the new
filename. One is a text value in cell B2, the other is a date in cell B1. I
have tried this many ways but cannot get it to work. Below is a modified
example that I found on the net:

Sub filesave()
Dim Path As String
Dim Rep As String
Dim RDate As Date
Path = "C:\MyFiles\"
Rep = Range("B2").Value
RDate = Range("B1").Value
ActiveWorkbook.SaveAs filename:=Path & Rep & RDate & ".xls", _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub


Please let me know what is causing this code to error out.

Thanks

Scott


You need to convert the date var to a string so it matches the SaveAs filename
data type! Try this instead...

Const sPath$ = "C:\MyFiles\"
ActiveWorkbook.SaveAs filename:= sPath & Range("B2") & Range("B1") & ".xls"

...as all the other args are default values and so not needed.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SaveAs Filename:=filename, FileFormat:=xlCSV Teddy[_3_] Excel Programming 2 May 29th 07 02:34 PM
SaveAs Macro - Build filename from cell contents HOW? Ch33baman2 Excel Programming 4 February 22nd 07 05:03 PM
SaveAs Cell.value=filename Helmut Excel Programming 4 November 23rd 06 03:26 PM
Set Saveas Filename from Cell Contents Using VBA Celtic_Avenger Excel Programming 1 September 5th 04 10:57 PM
Macro to open SaveAs... and change filename to cell value Andy Excel Programming 5 July 19th 04 12:23 PM


All times are GMT +1. The time now is 12:32 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017