Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Soon!)

Close Window (X)   
Excel VBA Course
[80% Discount] Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Today!)

Save As Csv Corrupts Date Format

Hi,

I'm having problems with date formats when using a macro to copy selected data to a new workbook and then save as a CSV file.

I've attached the macro to this post so you can see what is going on. Pushing the button will copy the contents of the first sheet of the macro workbook into a new workbook, and save as a CSV file with a specific filename, in a specific location.

What I need is for the contents of Column C (currently in format MMMYY) to be converted to "MMM-YY" format in the saved CSV file. (I've attached a reduced version of the macro for security reasons as it contains sensitive data but, the retrieval of the data in the macro is from another system and the date format retrieved is as in this file).

To verify the date format of the generated CSV file, i've been opening the file in notepad. A database app will be reading in the file so it is imperative that the date format is correctly represented in notepad. However, it isn't. Notepad shows the date as MMMYY.
However, if you open the generated CSV file in excel, the date format is displayed correctly.

The only way I can get the correct date format to display in notepad is to open the CSV file in Excel, re-save the file and accept the dialogue box that pops up warning me that some formatting may be lost if the file is saved in CSV format.

Hoping someone can help!

Many thanks in advance for your help!

Hannah

Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

(80% Discount Ends Soon!)

View Course




Similar Topics







I know this question has been asked a bajillion times, so I apologize for the redundancy.

I am working with an Excel spreadsheet and saving it as a .csv file in order to upload to an application that parses out the .csv data as transactions. The system requires .csv files, so this is how I need to save my doc (with this extension). I have been successful at preventing Excel from coverting that long number into scientific format. I have saved as a TXT file, pasted the longer number and it displays correctly. That is all good. But I have to save as a .csv. So if I do that, close the Excel window, and then open again (as the .csv file), the numbers are back to being displayed in scientific format. I have tried creating an Excel doc from scratch and entering text in Text format, to see if this created a cleaner file. But again, the second I save as .csv, close the window and then open that file up again, that dang scientific format is back.

Does anyone have any idea of how to work around this? Once I have successfully gotten the numbers to display as the long-chain number, how can I get them to "stick" so that they don't revert back to scientific format when I reopen the file?

Thanks so much for your help!


Can anyone help me with creating a macro that will save the worksheet as a pdf in a specific folder with the value of a cell and the date. This is what I have so far.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("H10").Value & Format(Date, "mmdd")

This saves it as the correct cell value + the date, but it saves it to my documents. I need it to save to a shared folder on a network drive.

Any help would be greatly appreciated.
THANKS


I have workbook in which I want to save a specific worksheet to a new file with only the values saved - all data in this worksheet are references to cells on another worksheet, which is using VLOOKUP to pull data from a database.

Found the following code and it gets me close, but it copies the cell references, not the values. It also allows me to specify the file name from a cell reference.

I want the new workbook file to simply be saved, not opened, and a message box to display stating where the file was saved (will always be in the same location on the LAN).

What modifications do I make to this to get this to work per above requirements?

Sub CopyMe()
Dim SaveMeAs As String
SaveMeAs = Sheets("Sheet1").Range("B2").Text
Sheets("Sheet3").Copy
ActiveWorkbook.SaveAs Filename:="C:\My Documents\" & SaveMeAs
End Sub


Hello,

I have a number of different files that I often need to run a macro on. In order for me to do it on the 75-100 files I have at any given time, I need to open one, run the macro, close and save, then open the next one.

Is it possible to write a macro that will start with the first file in a folder, open it and update links, run a macro, save and close, and open the next file in the folder until it has open all the files in the folder.

I have experience with creating macros that reference different workbooks, but not sure how to go about opening files with different filenames (without referencing the exact filename).

I'd like to be able to have basic code for opening, saving and closing, opening next file, saving and closing, etc. and input the macro I'd need to run in each file in the appropriate location. Is this possible? Any help is greatly appreciated!!

Thanks,
Jason


I was wondering if there is a way of getting a macro to open the "save as" dialogue box and then have it fill in the "file name" field.

I dont want it to actually make the save though as I want the user to have the chance to check that the save location is correct and the option to change the filename if required.

Cheers.


I have imported a DBF file into Excel and have a column of dates that are missing the leading zero on single didgit months. When I try to us the custom format of mm/dd/yyyy it doesn't work (interestingly, after I select that format if I click on an individual cell it changes to the right format).

Does anyone know a better way to do this?

Thanks in advance!


I have an excel form, I want my users to be able to open the form, fill in appropriate data, then click the save button. When the button is clicked, the data in the form should be saved to a new file and then the original form should be reset (clear all values) for a new set of data entry. When the data is saved, I want the new excel sheet to be saved to a location specified within the macro so the user doesn't have any options and I want the title of the new file to be the value of one of the cells.

I'm new to macros and would appreciate any help this board can offer. Thanks...


I have a file that became too big due to phantom bloat, unused range saved by Excel and all that kind of reasons. Thank to previous posts on that board tackling that issue, I was able to find how to proceed to reduce the file back to its normal size.
But I did so in a beta file (test file). The real file has become so big (103MB!) that Excel cannot even open it anymore! The file contains archive info that we do not have anywhere else.
Is there anyway then to open the file or to reduce its size without opening it (through magics...)? I just honestly don't know how to retrieve that info before deleting that file.

Thank you very much for your help.


I have a VBMacro Excel file loaded on a Server that numerous people access. A Macro in this file creates a Copy of a specific Sheet within the Active Workbook and I want to Save it to the individual's Desktop.

How do I find out what the current User's desktop folder path is each time the Marco is run by a different User?

Example User's path: 'C:\Documents and Settings\jfarc\Desktop'

Where 'jfarc' is the name of the current User which, will of course change with every different User that runs the Macro.

Also, is there a way to pull out of Excel what is the current User's 'Options | General | Default File Location' entry? Which may differ from the above directory.

I am familiar with and use the following coding for Opening/Saving files to the current directory of the opened workbook, but it only gives the path of the existing Excel workbook and not the current User's Directory Path:

Dim wbThis As Workbook
Set wbThis = ThisWorkbook
ChDir wbThis.Path


Hey guys,

I'm trying to write a macro which prints to PDF and saves the file name as the contents of a cell. I've been looking through all the posts currently on this forum to get something working. I'm using the following code -

Sub PrintPDF()
Filename = "C:\Documents and Settings\samb\My Documents\" & ActiveSheet.Range("Z1").Value
SendKeys Filename & "{ENTER}", False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF:", Collate:=True
End Sub

The macro prints to PDF, but then it stops at the Save As stage, where I have to manually enter the name of the file and click Save. I want the macro to automatically name the file with the contents of cell Z1. I then want it to then automatically press enter. Any ideas where I'm going wrong? Any help would be much appreciated!


Arcangelo from Italy asks: How can I write an Excel VBA macro to save the current Excel file with a filename derived from cell A1?
This macro is amazingly straight-forward:

Public Sub SaveAsA1()
ThisFile = Range("A1").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub
I'm totally lost on this "amazingly straight-forward" macro!!!! Could someone help if I tell you the SAVE location? It's T:\COMMISSIONING\IJT\TIMELOG project\Staff#1. I'm just not certain what value I'm replacing in the Macro above.

Any help appreciated..



I have an Excel workbook that was created by a former coworker. It includes a macro that, among other things, displays a message box about the 2008 file. The macro runs as soon as the file is opened. I'd like to access that macro to correct the date to 2011 and see what else, if anything, it's doing for me (it doesn't appear to do much). I can find references to creating macros to hide and unhide rows/columns and I found ways to delete all macros in a workbook, but I cannot find anything about unhiding a macro without knowing its name.

Does anyone know of a way to unhide this macro?

Thanks!


Hi all,

I've had a long search through your pages to see if this question has been answered before but having browsed through about 50 pages worth of threads I couldn't see anything, but if I am repeating prior information I do apologise.

I've written a macro that is relatively simple. It just takes some information in one format, rearranges it, adds some formatting and performs some calculations. Nothing incredibly fancy but it works fine on my computer.

Now, I need to share this macro with some other people, so basically I've just sent that excel file on to the people that need to use it. Should be fine and in most cases it is, however there is one user who although they can open the file, can't seem to get the macro to run properly.

It seems to get a small way through the macro but then stop with no error messages or any sign that it hasn't completed properly.

I have checked Macro Security level and that is the same as mine, Tools - Add-Ins is the same, In Visual Basic, Tools - References is the same as mine. It is the same Operating system and the same version of Excel.

I have even signed into this person's computer as myself (it's a big company network thing) and tried to run the macro and it works fine, so there is nothing wrong with the hardware.

I've googled and searched and tried everything I can think of but I'm no closer to solving this problem, so if anyone has read through this wall of text and can come up with a possible solution, that would be greatly appreciated to save me from tearing ALL my hair out!

Thanks very much for your time.


I need help desparately. Last night I spent several hours working on an
Excel 2002 spreadsheet and it has disappeared.

I saved it routinely as I was making many changes updates etc. I opened
original file --stuff.xls --- made changes and saved it as again as
stuff.xls. I have done this several times before.

Today, when I go to open stuff.xls, it is nowhere to be found!!!!! I have
done a search, for any file modified since Thurs and nothing has appeared. I
have checked the recycle bin, this did not go into auto recover nothing of
the like. I am just traumatized, because I have NEVER had anything like this
happen before.

I even thought perhaps I didn't save it as often as I thought, (I know that
I did) but I remembered that I saved it at least once and I can't even find
an Excel file that has been modified since Thurs!!!

Does anyone have any idea what could have happened to this file?? BTW, I
have prayed, rebooted, run spybot, called witch doctor, all with no success.

I have never had a file to just disappear with no trace. Any help would be
greatly appreciated.

Thanks in advance---



I did a bit of browsing on this problem. Found others suffering the same but haven't found any conclusive answer yet.

Every so often when I attempt to save a file, (including save as), Excel won'r let me. By won't let me I mean:

using Save doesn't appear to do anything using Save As doesn't either do anything, the dialog is not displayed and if I am doing via the File menu then the File menu is exited and the previous ribbon tab is displayed (i.ethe one I was on before clicking 'File') if I close the workbook I am prompted to save, close without saving or cancel. Clicking save just invokes the same msgbox again. I can't work out when it goes into this mode. Some days I can work without this problem, other days I encounter this 2 or 3 times.

The only thing I could suspect was I think this started around about the time I installed xlDennis' code library. I have uninstalled the addin and so far so good, but I cannot categorically say that this was the cause.

Anyone have any idea?

Cheers
Jon

Edit: I have read this: http://support.microsoft.com/kb/271513
Doesn't seem to cover the issue I describe


I have a template that automatically populates the date field with the
current date when opened, using today(). Users then Save As to have an
archive copy of their spreadsheet. However, when they go back to access
their saved sheet, the date changes from the created date to the current
date. How do I stop this in Excel?

I know that Word lets you do this by changing the code from {DATE} to
{CREATEDATE}, but I can't find a similar setting in Excel.

Please help and thank you!



We have an Excel spreadsheet that sits on the network.
People need to open the file to be able to sign up for various duty rosters.
We would like for the file to open for the first person.
And then for any others after that, get a message that the file is in use
WITHOUT the option to open a read-only copy.
Our staff can't read and they keep opening additional copies of the file!
I have read about sharing the file and I don't think that would make things
any better.
Thanks,
Lynn



Hi All,

I've been struggling with this for a while now and can't believe how hard it is!. I've searched on this site and on others to get some clarification but to no avail.

It's pretty simple really. I have a user form which contains a tex box for a user to input the date I want the format to be dd/mm/yyyy but can't find out how to set the format of the text box to this.

Please can someone give me a bit of guidence or link me to a good rescource if missed it in the search.

Thanks in advance.

G


How do I grant only specific people to be able to open, view, edit an excel file? I don't want anybody else besides these two people to be able to even open the file, so I'm not talking about password protecting to prohibit editing a file. I would assume I need to go to the properties, security settings but I'm not sure?


Hi all,

I've been getting this error on occasion recently.

- It's a shared document
- There is usually 10-12 people using the file at the same time

I've read on the Microsoft Help site that the issue is that somebody is accessing the file while another is trying to open it.

Is there a way, perhaps through VBA, that I can not allow a user to save while somebody else is opening the document? Other suggestions on how to avoid this error?

Thanks!