Macro To Save As CSV File With Extra Commas

Mar 6, 2012

I created a macro to save an active sheet as a CSV file. Two issues arise out of that effort. One is that I can't seem to get the environment variable right to allow any user to save the file to the root of their My Documents folder on their home directory in a domain environment. That way the template will work for everyone and requires no user intervention. Tried using the % with HOMEPATH, but I must somehow have the syntax off.

Secondly, the file being saved as a CSV file is creating a huge several megabyte file with empty lines of commas.

Sub Macro2()
Sheets("Results").Select
ActiveWorkbook.SaveAs Filename:= _
"\%HOMEPATH%My DocumentsTesting.csv", FileFormat:=xlCSVMSDOS, _
CreateBackup:=False
Application.WindowState = xlNormal
End Sub

Thought it should be CSV MS DOS file type, but that doesn't seem to work either. I only want to save the rows with data on the active sheet to a CSV file, which will not have all the extra lines of commas.

View 1 Replies


ADVERTISEMENT

Save Excel File To CSV With Blank Cells With Formula Without Getting Commas?

Mar 20, 2014

I am working on a excel file that has a sheet that i want to convert to .csv. The sheet has formula's for the first three column that are warped an if statement that returns "" (blank) if certain conditions are not meet. However when i got to save the file as a csv i get a file with ",,,,,,," where there is no data. I would like to save csv and have only commas where this data.

Example
When i convert a sheet like
Firstname Lastname Password
Tim Cats Bo3n9x

it saves as
Firstname Lastname Password
Tim Cats Bo3n9x
,,,
,,,
,,,

In the .csv file.

Files i am current working on are attached.

View 13 Replies View Related

Create CSV Without Extra Commas

Feb 1, 2010

create a macro that will create csv files for each worksheet of a workbook. The Macro works great. But i do have a requirement where i do not need extra commas (,) in the CSV. When the CSV are created for each worksheet, it adds additional commas in the end and my shell script throws it away becuase of the additional commas.

I have attached 3 files -
csvtest.xls - this has the macro which creates individual csv files for EACH worksheet in the workbook........

View 4 Replies View Related

Remote Extra Commas From Column

Jul 21, 2014

I am looking for VBA code to remove extra commas from d column.

Missing
, , , 1617614,
, , , 1676652,
, , , , 1676664, 1676665, 1676666, , 1676668, , 1676670
, , 1591813,,,,
, , , 1591113,

View 9 Replies View Related

Save In Excel With Commas

May 16, 2009

Is there a way to save as an excel file containing a list of words inone column so when I open it in Notepad I get the words in one line separated by commas?

View 6 Replies View Related

Save As Macro :: Save File To Folder

Jan 17, 2010

I have a macro that copy one sheet of the Active workbook and sends it via email.

I need to add a code in this sheet so when one opens it from the email, with a command button to be able to save the file to specific, fixed folder on the local network with it’s original file name.

View 10 Replies View Related

Macro To Save Worksheet As New File Then Return To Original File

Mar 14, 2014

I have a macro which successully saves a worksheet as new file to another file path....(below)...but I can't figure out how to close this new file and return to the original file...

Dim myPath As String, fName As String
myPath = Sheets("Date").Range("C8").Text
fName = Sheets("Date").Range("C9").Text
Sheets("Sage CSV File").Copy
With ActiveWorkbook
.SaveAs Filename:=myPath & fName
End With'

View 2 Replies View Related

Macro To Select All Worksheet And Save File As File Name?

Mar 6, 2012

I want to create a macro that will select all the worksheets (names and quantity will vary) and saves the file as the current file's name but in PDF. Since I only know how to record a macro it specifies the worksheet names but I need it for various workbooks. The name will vary plus the number of tabs can go anywhere from 3 to 40.

View 9 Replies View Related

Macro: Save File & Automatically Overwrite Old File

Oct 11, 2006

I presently have a macro that, when run, takes to conents of C4 and C6 and saves a new version of the file being worked on into a folder on my desktop. I love the macro with the exception of one part: I don't want to be prompted to overwrite the file if it already exists. How can I change this macro so that, when pressed, it overwrites the file without prompting the user and waiting for their answer?

Here is the current

Sub SaveIt() ...

View 3 Replies View Related

Macro To Save File - Save Name From Cell Containing Date Using Different Date Formatting

Aug 15, 2014

I am trying to create a macro to run from a form button, within a report, to save a file to a variable file path and name depending on the date value in cell B5.

The format of B5 looks like - 13/08/2014 16:39

The file path has folders for each year in format "yyyy" with each year having sub folders for each month in format "mm".

The file name is just the date only and is formatted "dd.mm.yy" e.g. 13.08.14

I have tried the code below in various permutations but always end up with an error - Method 'SaveAs' of object '_Workbook' failed.

[Code] ......

View 3 Replies View Related

Macro To Save File As PDF

Dec 3, 2011

I would like to 'save as' my current open file with a new name equal to the value in cell A1 of the active sheet, and save it to C:Apps. I would also like to save it is a pdf with the same name and to the same location. Would that be possible in the same macro?

View 4 Replies View Related

Save As File Name-Macro

Mar 23, 2009

I have a macro recorded that saves the workbook to my desktop and then saves an additional copy to a shared drive. Right now it wants to save my file as the same name every time and ask to replace the existing copy.

What I would like to tell it to do is make the save as name reference cell D10. Unfortunatley, I don't know how to tell it to do that in visual basic.

View 9 Replies View Related

Tab Delimited File Saves Commas As Dot

Jan 30, 2008

I'm using Excel 2000 swedish version, that is we use comma to seperate decimals not dot. I have a macro that saves a sheet as .txt file (tab delimited). However in the sheet (journal voucher) I need to enter a couple of numbers all with 2 decimal, that is 5 is 5,00 and so on. But then, when I save the sheet to a .txt file using a macro all the commas are saved as dots and since I'm importing the file to SAP it will not accept the dots. Is there some way ( changes in macro) I can prevent the commas from becoming dots when I save the .txt file?

The save macro is the commomly used

ActiveWorkbook.SaveAs Filename:= _
"C:Documents and Settings& strText & ".txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Close
ThisWorkbook.Activate

View 4 Replies View Related

Save VBA Macro Button In CSV File

May 23, 2014

Would I be able to save a Macro button in a CSV File since every time I open spreadsheet it disappears and my system only accepts CSV.

View 7 Replies View Related

Save One Worksheet As A .CSV File With Macro

Jan 7, 2010

So I am trying to design a workbook that has two worksheets... one with instructions and a button for users to click to "Save as .CSV File", another for the data that will go into that CSV file.

Here are the Macro requirements:
1. The user will be prompted for the File & Location to save the .CSV file
2. If they click 'Cancel', no changes will be made (and unlike my current code, it won't ask them to debug).
3. Confirmation of the filename is not necessary even though it's currently included in my Macro
4. The file will automatically "reopen" so that they only see the new .CSV file without the original Instruction tab.

View 2 Replies View Related

Macro To Save A File With Last Row Counter

Feb 5, 2013

The code I wrote to save a file with the last row counter and month counter looks as follows (this was working on my Mac)

ActiveWorkbook.SaveAs FileName:= _
"Macintosh HD:Users:myratriegaardt:Desktop:Quotes" & M & ":Quote" & LastRow & ":QuoteHG" & LastRow & ".xlsm", FileFormat _
:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

I converted it to put on Windows,

ActiveWorkbook.SaveAs FileName:= _
"C:UsersCape CratingDesktopQuotes" & M & "Quote" & LastRow & "QuoteHG" & LastRow & ".xlsm", FileFormat _
:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

This however gives an error.

View 2 Replies View Related

Macro Cannot Save File With Date In Name

Nov 7, 2006

The following macro is returning a 1004 runtime error.

FName1 is a book title PHOTOSHOP MADE EASY which is trimmed to remove blank spaces at the end.

FName2 is the date =Today() formatted dd/mm/yy.

When the 1004 error appears the filepath shown looks like this ...

View 5 Replies View Related

Macro To Save File Every X Minutes

Sep 13, 2007

Macro to save the worksheet every 5min

A macros is needed to:

1. save the worksheet under the same name
(& variation: under diff name - e.g. quotes_HHMM.xls ,
where HH is hour and MM is minutes of the time the file is saved)

2. every 5 minutes (the file is opened at 9AM and closed at 6PM manually)

I have a file, where the financial data is streamed in from Reuters.

View 7 Replies View Related

Macro To Save Workbook By Referencing Cell In Another Workbook For File Name And File Path

Mar 21, 2013

I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...

View 5 Replies View Related

Prevent Multiple Commas In Text File

Nov 30, 2007

I have a spreadsheet that is produced by a macro. This spreadsheet has a table of 30 rows and 24 columns pasted into it from another spreadsheet. The first 6 rows of vary between containing 1 and 6 columns of data. The next 17 rows always have 24 columns of data. The last row has 1 column of data. The macro saves this spreadsheet as a .csv file. The macro works great, but when I open the .csv file in notepad all the rows have 23 commas, even for the rows with only a few columns of data. Here is a shorten example of the file when opened in Notepad....

<GTS-IPL-Customer>,,,,,,,,,,,,,,,,,,,,,,,,
H1,October,2007,,,Normal,,,,,,,,,,,,,,,,,,,
H2,000426,,,,,,,,,,,,,,,,,,,,,,,
H3,2.72283E+12,,,,,,,,,,,,,,,,,,,,,,,
H4,88100031,,,,,,,,,,,,,,,,,,,,,,,
H5,NBPL,,,,,,,,,,,,,,,,,,,,,,,
D,10012007,0,312,2,310,304,1006,306,306,4,4,31,4,0,0,0,,,,,,,5.805,-23.22
D,10022007,0,334,2,332,330,1009,333,639,-1,3,33,1,0,0,0,,,,,,,6.165,6.165
D,10032007,0,316,2,314,302,1045,316,955,-2,1,31,2,0,0,0,,,,,,,6.675,13.35


Is there anyway to have the macro not insert the commas in the first 6 rows for columns on the right side of the last column of data? I need the data to be formatted in the format shown below.

<GTS-IPL-Customer>
H1,October,2007,,,Normal
H2,000426
H3,2.72283E.....................

View 9 Replies View Related

Auto Save To A File Via Macro Or Program

Apr 11, 2008

Is It Possible To Auto Save To A File Via A Macro?

What I Want To Do Is To Auto Save Directly To A Folder On Our System Via A Macro.

The Problem Is That Firstly It Will Need To Look Up A Cell ( D8 ) To Find Out The Job Number

Then It Will Have To Find The Folder On Our System ( Its On Desktop Under Job File Folder ) Then Comes The Hard Bit It Will Need To Locate That Job Number ( Cell D8 ) To Look Up The Relevant Folder To Put It In. All The Job Files Are Labelled Up Eg.( J2663 - Parry ) Then In That Folder Is A Sub Folder Called Docs Which It Needs To Be Saved In

View 14 Replies View Related

Excel 2010 :: SAVE AS File Name Using Macro?

Aug 13, 2012

USING EXCEL 2010

I am trying to record a macro where the last step is to SAVE AS the file. I want it to use the current date as the end of the file name.
i.e.

Daily OST -- 2012-08-10
Daily OST -- 2012-08-11
Daily OST -- 2012-08-12
etc...

This is what is currently in the macro but all it gives me is the first part "Daily OST --". How to fix?

ActiveWorkbook.SaveAs Filename:= _
"Y:ProjectsProgram ManagementPIODaily ReportsDaily OST -- " & DateString & ".xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

View 6 Replies View Related

Macro To Save A File To Specific Folder?

Dec 7, 2012

I have a folder called "Report" at C: and inside this folder there are 12 folder named from 1 to 12 ("1","2",...,"12")

I need a macro to save my workbook as a file in C:Report(one of these folders depending on the month of a certain cell)

i.e. if the date in this cell is 8/12/2012 .. then save my file in C:Report12*.xlsm

This is the code i use

Code:
Private Sub CommandButton1_Click()
Dim fname As String
fname = Format$(Range("S8"), "dd-mm-yyyy")
On Error Resume Next
ActiveWorkbook.SaveAs Filename:="C:Reort" & fname & ".xlsm"
End Sub

So what i need now is what to insert after C:Report

View 9 Replies View Related

Macro (VBA) To Save File To Web Directory (SharePoint)

Jun 17, 2014

I have recorded the below code that functions fine from my computer. My issue is that I need to export/import this macro to other workstations who may have the save to location mapped to a different drive letter. Is there a way to replace the drive letter with the URL (SharePoint)?

ChDir "E:3. CRQsRemedy Dumps"
ActiveWorkbook.SaveAs Filename:="E:3. CRQsRemedy DumpsToday_CRQ_8Dump.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Save To Directory: \server_1Dir_1Dir_2FolderToday_CRQ_8Dump.xls
Permissions are in place.

View 1 Replies View Related

Macro To Create New Folder And Save File

May 27, 2009

I want to create a macro that will create a new folder called "Fungicide Quotes" under my documents and will save the workbook using the cell reference d4:f4 for the file name, which are merged cells. I have tried the following but can't get it to work. Any help would be appreciated, Thanks

Sub Save_wrkbk()

Dim strFilename, strDirname, strPathname, strDefpath As String
On Error Resume Next
strDirname = "Fungicide Quotes"

strFilename = Range("d4:f4").Value
strDefpath = "C:My Documents"
If IsEmpty(Filename) Then Exit Sub

MkDir strDefpath & strDirname
strPathname = strDefpath & strDirname & "" & strFilename.......................

View 9 Replies View Related

Macro To Save Workbook/File As Cell Value

Sep 15, 2006

I need to be able to save a workbook by running a marco and it save the file automatically by pulling what ever the value of cell A1 is.

Ex: I want to run the macro and it save my excel workbook in C:Documents as (Value of Cell A1) What code would you put into Visual Basic.

View 3 Replies View Related

Macro To Access/edit/save A Text File

Apr 21, 2008

I have an xl doc in which one of the sheet's column A changes value every 1 hour...

What I would like to know is.. if there is a method in which i can copy these values from column A to a text file every hour...

The range of cells containing values in Column A also varies every hour.

Also, the old values in the text file needs to get deleted before the new values are updated every hour.

View 14 Replies View Related

Save As Macro With Location Prompt But Fixed File Name ?

Dec 29, 2008

For a sheet that many non-expert users will use on different systems I need a macro that let's them save, print and send the results of their work. So I made a macro that makes a copy of only 1 sheet of the workbook and saves it with a given name to a given location. The problem is that I want a location prompt to ask the user where they want the file saved, while giving/suggesting them a fixed filename. A lot of different users will make and use their sheets so I need a certain naming policy to manage all the files. (date, location, etc)

View 4 Replies View Related

Excel 2010 :: Macro To Save As PDF - Using File Name In Cell

Jun 24, 2012

I recorded a macro to save my file as PDF and assigned the macro to a button. Easy. Now I would like to change my macro to pause when the file is saving to allow the user to enter a file name. If that is not possible, I'd like to reference a cell to use as the file name. I have gone through other posts and tried changing my macro, but always get an error. I am using Excel 2010. Following is my code for saving to pdf.

Sub SavePDF()
'
' SavePDF Macro
'

[Code]....

View 9 Replies View Related

Excel 2010 :: Macro To Save As Encrypted File?

Jan 21, 2014

I am looking for a macro to "Save as" the current open file with an encrypted password using the highest protection algorithm in Excel 2010.

This should be a background process without user interacting with any dialogs and the new file should have "_enc" added to the end of the filename.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved