Hi, I have written a VBA program that runs on Excel and puts data on the excel sheet. This runs everyday. I want to be adding the dates to the files, this date is gotten from the excel sheet that uploads into the report excel file. Below is the Code I wrote which doesnt work, please could you help me -
Sheets("Matrix sheet").Select
-
Today = Cells(1, 1) 'The location of the date on the raw sheet
-
Today = Format(Today, "dd-mm-yyyy")
-
Windows("cells.xls").Close savechanges:=True, Filename:="c:\Daily_Alerts\Daily Alerts_ " & Today & " "
-
5 4631
Apart from the slightly questionable habit of using the same variable (Today) to hold data of two different formats (date and string) what seems to be the problem? I thought the code looked alright.
Hint: the problem is not "it doesn't work". You need to be specific. For example, have you stopped the code at the point of executing the Close and examined the string that is being supplied as the filename? What is the string? Is an error occuring? If so, what are the error details?
I have resolved it. The issue was that .xls was not included in the file name. Thanks for all your help.
Below is the code that works now -
Dim Today As Date
-
Dim Todayb As String
-
Today = Cells(1, 1)
-
Todayb = Format(Today, "dd-mm-yyyy")
-
Todayb = Todayb & ".xls"
-
Windows("cells.xls").Close savechanges:=True, Filename:="c:\Daily_Alerts\Daily Alerts_" & Todayb & " "
-
Ok, glad to see you got it sorted out. Debugging usually ends up being about checking every little detail like that.
I am attempting to add a date to a file name. I want the first file to end with
30-june-07, and each file going forward to end with the end of the month. I keep receiving the 'Invalid procedure call ir argument' error. Here is my code: - Sheets("Portfolio").Select
-
Sheets("Portfolio").Copy
-
ChDir "P:\Conduits\Servicing\Port Stats\Allied\Archive\Portfolio"
-
ActiveWorkbook.SaveAs Filename:= _
-
"P:\Conduits\Servicing\Port Stats\Allied\Archive\Portfolio\Portfolio_" & DateAdd(m, 1, 30 - Jun - 7) & " ", FileFormat:= _
-
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
-
ActiveWindow.Close
-
Sheets("WA").Select
-
Sheets("WA").Copy
-
ChDir "P:\Conduits\Servicing\Port Stats\Allied\Archive\WA"
-
ActiveWorkbook.SaveAs Filename:= _
-
"P:\Conduits\Servicing\Port Stats\Allied\Archive\WA\WA_" & DateAdd(m, 1, 30 - Jun - 7) & " ", FileFormat:= _
-
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
-
, CreateBackup:=False
-
ActiveWindow.Close
-
End Sub
Thanks for your help!
You didn't say which line produced the error. However, I think this function call... DateAdd(m, 1, 30 - Jun - 7)
probably has some problems. You are telling it to take 30, subtract some variable called Jun, then subtract 7, and treat the result as a date value. Somehow, I don't think this is what you intended.
You might (I haven't checked) get away with writing it this way, if you put # delimiters around your literal value. For example... DateAdd(m, 1, #30-Jun-07#)
Another thing to consider is this. The DateAdd function will return a date value. You are then placing it in a string, which forces VB to convert it to a string. If you want it to be presented in a particular format (eg DD-MMM-YY) then you might need to use something like the Format() function to force it. However, you may be perfectly happy with your computer's default format, in which case don't worry about it. But, if you need to be certain the format won't change when run on someone else's system, you had best enforce your format.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: 6thirty |
last post by:
Hi,
I've created a stocktaking database using Access XP. This is indexed by two
fields - part number and shelf location.
I am currently inputting all the data via a form. When I have entered a...
|
by: Ankit Aneja |
last post by:
I have a strange situation and I have no idea how to solve this.
Its a Recruitment Search Page,in the Admin Page, for every button click
event the Admin Person has to create a checkbox on the users...
|
by: markryde |
last post by:
Hello,
I am trying to add the current date to a file name in python script
like thus:
import os
import sys
import rpm
import time
import datetime
|
by: xxtokenxx |
last post by:
I am writing a birthday list for a site for my school. While developing it. Currently the code for the output of birthdays is:
<html><head>
<title>Birthday List</title><script...
|
by: aRTx |
last post by:
<?
/*
Directory Listing Script - Version 2
====================================
Script Author: Artani <artan_p@msn.com>. www.artxcenter.com
REQUIREMENTS
============
This script requires...
|
by: David |
last post by:
I'm trying to add a date and time to an html form, but I'm having a
bit of trouble getting it working. Any suggestions?
Thanks!
~ David (merlin001_at_gmail_dot_com)
|
by: tolkienarda |
last post by:
hi all
I am working on a php driven database program for a literacy program, it will allow them to keep track of classes and students, the part i am strugling with is adding new classes, the...
|
by: Ken Fine |
last post by:
I want to add the security question and answer security feature to the
ChangePassword control. I am aware that this functionality is built into the
PasswordRecovery tool. I have implemented the...
|
by: swethak |
last post by:
Hi,
I am desiging the calendar application for that purpose i used the below code. But it is for only displys calendar. And also i want to add the events to calendar. In that code displys the...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |