Free Microsoft Excel 2013 Quick Reference

VBA to save CSV file as XLS, delete CSV

Is there a way to save a CSV file as XLS in the same directory, then delete the old CSV file?


Hi,

This should not be hard, but either I am stoopid or Excel is.

In VBA, I want to save a copy of my current worksheet as a CSV file
and not change anything about the current workbook. It seems to me
that "SaveAs filename:=whatever.csv fileformat:=xlCSV" saves the file
as XLS, and converts the current file to CSV !

I have tried various work-arounds such as using SaveCopyAs or
reloading the file from the original, but end up with all sorts of
messes such as a CSV file containing .XLS data and my current file not
being able to be written as Excel think it is a CSV file containing
multiple sheets.

TIA for any advice on how to do this, short of writing out the CSV
data in my own code :-(

Richard

Hello

when i open csv file i run this macro and i want to save the file as .xls file

my macro is :

Sub Macro1()

Directory = "E:TempDB"
ThisFile = InputBox("Writh your file name") & "." & "" & Format(Date,
"dd.mm.yy") & ".xls"
ActiveWorkbook.SaveAs Filename:=Directory & ThisFile

MsgBox _
"      Your file save in " & vbCrLf & _
" " & vbCrLf & _
(ThisFile), vbInformation

End Sub
Where insert the line "FileFormat:=xlExcel8" ??

Thank you

How do I end off a written macro to save my file as a PDF using a specific
field as the file name ?

I am starting to delve into Excel 2007 and was wondering if you could use VBA to save a file with a password. I know you can use VBA to save a file, but what about with a password? Meaning, when you open the file, you have to have the password for the file to actually open....

Hi there,

I need to save multiple worksheets in an excel file to individual CSV files. Right now, I am doing this manually once a week, but since there are over 30 worksheets, this is obviously quite cumbersome.
Attached is an example of the logic.

I have 2 "master sheets" in the workbook that do the heavy lifting of the analysis, populating the 30 worksheets with updated data, once I ran my update links etc. What I would need is some sort of VBA code that can easily save each worksheet as a CSV file in the same folder that the master file resides in, using the worksheet name as the file name, requiring no human interaction.

Do you think that is possible?

All worksheets that need to be exported start with the word "CSV", so that could be an automatic criteria for the macro to understand which sheets need to get exported.... I couldn't find a similar solution in the forum unfortunately, which struck me as weird, as I don't believe to have such a unique requirement. If there's anything out there already that does what I need, please post the link, it's very much appreciated!

Best regards,
Titus

I have a macro which currently saves a file as a ".csv" file. I would like to change this to save the file as a ".xls 97-2003 workbook" see below for the current coding.

Public Sub CopyANdSave()

Application.DisplayAlerts = False
    
   
Dim strActive As String

strActive = ActiveWorkbook.Name

Sheets("Sheet1").Copy
safname = Application.GetSaveAsFilename(FileFilter:="CSV File (*.csv), *.csv")
        If safname <> False Then
        
        ActiveWorkbook.SaveAs safname, xlCSV
        ActiveWorkbook.Close SaveChanges:=False
        'Workbooks(safname).Close
        Workbooks(strActive).Activate
        
        Application.DisplayAlerts = True

        
        Exit Sub
End If

End Sub


Hi

I’m using the follow code to rename a sheet, change a cell value, remove all formulas from sheet , then save the file to a specific folder and with the name of the file as the value of a cell.

My Problem is this a “CSV” file and it must be safe as excel worksheet but the code does not.

At the moment it save the file as a CSV file and change the sheet name to the file name – that’s not what I want.

Please help

Neels

	VB:
	
 RenameSheet_SavebookFASE3() 
    Dim FPATH As String 
     
    Application.DisplayAlerts = False 
     
    Sheets("DATA2").Select 
    Sheets("DATA2").Name = "Sheet1" 
    Range("C1").Select 
    Selection.End(xlDown).Select 
    ActiveCell.FormulaR1C1 = 0 
     
    Cells.Select 
    Selection.Copy 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Application.CutCopyMode = False 
     
    Range("A1").Select 
     
    FPATH = "C:Documents and SettingsNeels BorstlapMy DocumentsASCANFIN REPORTSDAILY REPORTSCombine Data" 
     
    ActiveWorkbook.SaveAs FPATH & Range("B1").Value 
    ActiveWorkbook.Close 
    Application.DisplayAlerts = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I've spent alot of time the past couple days trying to get my code to save a file as part of the macro. The filename should be the value of the contents of a single cell. I've got that to work with the following code (compliments of Norie---thanks!):

	VB:
	
 
 
strFileName = Sheet1.Range("G1").Value 
 
strFileName = Application.GetSaveAsFilename(strFileName) 
 
ActiveWorkbook.SaveAs strFileName 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but the problem is that the file won't save as a .xls file. When the Save As box comes up, the filename has quotes around it (for example: "jf123-12345 - Request # 1- Traditional", and the only filetype option is "All Files(*.*)". The formula in G1 is

=CONCATENATE(C8,"- ",C10, "-"," Request # ",G6,"- ",C12)

I don't know if that has anything to do with the wierd file type or not, but I am really stumped. I would really appreciate any help you can give me!

Hi,

I have excel sheet, where there are hyperlinks and when I click the hyperlinks, a csv files opens, I need to save those files as xls files in a folder.

Is there a macro which automatically triggers hyperlinks, and saves the files as xls in my desktop?

I do know if this is possible, please ignore if this is not possible...

Arvind

In the excel worksheet, I want to be able to record a macro so that everytime I hit 'control a' it saves the file as 'Cancellation Filing RR ###'. The ### are where I would like it to go to the next number. The file name is not found any where in the worksheet.

The folder location is y:/Dellserver/Newdell d/Users/rhagos

Oh by the way, what's the easiest way to learn VBA on my own so I don't have to keep relying on you guys in the forum. Ya'll do an excellent job, but I'd like to do this myself. Thanks

-Reese Hopkins

How to ask user about path and open synteza.csv file as column separated view
like synteza.xls?

Workboks.Open open it only as csv.

I ask to makro of course.

Thanks and sory for fatal English.

Hi,

I am looking for VBA code to save my Xla file after running the code.
Actually i want to delete code from a Xla file after one run. If i open a new workbook and re run the macro its working. To avoid that i want to save Xla file permanently to all workbooks.

Hi

I am recording macro and want to save the new macro- recorded sheet as the
same name as the sheet tab.
I do have the tab name in one of the cells A1, using =cell("filename",A1)
Example : sheet name week1, want to save new file as week1

I Can copy and paste into existing macro
thanks in advance

brian

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200601/1

Hi Everyone,
Can someone help me to add a password to save a file with. the code I have so far is as follows.

It needs to be saved in this particular folder with a passowrd that I will specify. The password does not change so I have that as part of the macro. The file name can also be todaysfile

    
ActiveWorkbook.SaveAs Filename:= _
        "Y:filefolder11filefolder2filefolder3Ifilefolder4todaysfile.xlsx", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False


Hi there

i have a macro that when i run it it saves Sheet 3 as Sel_FG.csv , i would like it to save Sheet 3 as the 1st 3 letters of cell A4 on ODDS Setup tab

This is the bit of code i already use, so it always saves that tab as SEL_FG.CSV i would like it to take the name it saves it by from Cell A4 on another tab which is called ODDS SETUP

PHP Code: 
Sheets("Sheet3").Select

    ActiveWorkbook.SaveAs Filename:="P:SEL_FG.csv", FileFormat:=xlCSV, _
        CreateBackup:=False 

any help would be great

thanks

I need to save a file as a Tab-delimited text file and as you may be aware any fields that contain punctuation (line breaks, quotes, commas and periods) are saved by Excel with quote marks around the contents. There is no way to disable this function.

I need the file to be saved without these quote marks.

I found this VBA script that supposedly will save Excel spreadsheets as txt files without these added quote marks:

Sub
SaveExcelToTabDelimitedWithoutQuotes()
   Dim r    As Long
   Dim arr  As Variant
   Dim file As Variant

   file = Left(ActiveWorkbook.FullName, InStrRev(ActiveWorkbook.FullName, ".") - 1) & ".txt"
   Set file = CreateObject("Scripting.FileSystemObject").CreateTextFile(file, True, True)

   With ActiveSheet
      arr = .Range(.[A1], .UsedRange.Cells(.UsedRange.Cells.Count))
      For r = 1 To UBound(arr)
         file.WriteLine Join(WorksheetFunction.Index(arr, r), vbTab)
      Next
   End With

   file.Close
End Sub
Unfortunately when I run the script I get an error-
"Run-time error '13':
Type mismatch"

on the line:

file.WriteLine Join(WorksheetFunction.Index(arr, r), vbTab)

Any help would be greatly appreciated!

Please advise how to open CSV file in Excel without using "Text to column"
steps. Where I can make all needed settings in Excel in order CSV files being
opened immediately without making additional actions. Thank you.

My issue is that I have a macro to copy data from one file to another - but
the first file can have different names.
So - How do I create a macro in Excel to save the file with a new file name,
or how do I make the file that I start the macro in - read the current file
name into the macro so it keeps toggling back to this file. The second file
will always have the same name.

Thanks,
Yosef

I have a load of spreadsheets that I need to file in the same folder.
The problem is each one has the same filename. Does anyone know how to
save a file as the date that it was created on thus saving me having to
go through 100's of files and do it manually.

Thanks

Dave Woodgate

I know that Excel allows me to save my file as an HTML file. I have a spreadsheet with data pulled from MS Query (datawarehouse tables). In cell A1 I have a parameter input value where the user can input the value and the query will refresh automatically pulling the correct data.

Is it possible to save the file as HTML, then for cell A1 to still be an variable user input value, and the data to update on the page? I know it's possibly using tools like SQL Server, etc, but I'm hoping to create reports that I can publish to our intranet website that users can use, plus by me creating these it's much faster than submitting a request to our programmers as they are backed up with so many requests as it is.

I've tried publishing the Excel file to HTML and I see the data, but that's it - I can only see the hard-coded data and it's not a dynamic report any more.

hi
i am a new member looking for some help in my problem
problem description:
i have to save .csv file as .xls after some opertaions
i opened the .csv file in excel through vb. then i converted it to .xls. i have a column named timestamp having the format yyyymmddhhnnss like 20050214094543 which i have to save after subtracting 7 hours in the same format
now i converted 20050214094543 to date format like 2005-02-14 09:45:43 using sql query and saved the file again successfully and set the column dataformat as custom yyyy-mm-dd hh:nn:ss . but the problem arises when i again access for subtracting 7 hours from the converted date ,sql query successfully subtracts 7 hours but on trying to save i get an error message while if i replace the same query with simple 'select * ... query without any date conversions it gets saved successfully. can anyone have a solution for my problem?it's very urgent

When using a French version of Excel XP I am running into a problem with the
save as feature. I am attempting to save a contact list as a comma separated
file so users using older versions of office will be able to import the list
into outlook. When I try to save the file as a comma separated file it uses
a semi-colon instead of a simple comma. I believe this is strictly a
language issue and need to know if there is a fix or a setting I can change
somewhere. Please help

saving problem in excel

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

hi
i am a new member looking for some help in my problem
problem description:
i have to save .csv file as .xls after some opertaions
i opened the .csv file in excel through vb. then i converted it to .xls. i have a column named timestamp having the format yyyymmddhhnnss like 20050214094543 which i have to save after subtracting 7 hours in the same format
now i converted 20050214094543 to date format like 2005-02-14 09:45:43 using sql query and saved the file again successfully and set the column dataformat as custom yyyy-mm-dd hh:nn:ss . but the problem arises when i again access for subtracting 7 hours from the converted date ,sql query successfully subtracts 7 hours but on trying to save i get an error message(returns recordset with all the records but wiht E_Fail status resulting in 'Range of ... failed' error. while if i replace the same query with simple 'select * ... query without any date conversions it gets saved successfully. can anyone have a solution for my problem?it's very urgent

Hi guys,

I've wrote a bit of code that (basically) will take a sheet, save it in a temp directory then attach it to a new message in outlook.

I can't however make it save the file as a CSV file format which is what i need - not xls.

My code is as follows:

Sub EmailWithOutlook()
'Variable declaration

    Dim oApp As Object, _
        oMail As Object, _
        WB As Workbook, _
        FileName As String
        
  On Error GoTo mailerr
 
'Turn off screen updating
    Application.ScreenUpdating = False
    
'Make a copy of the active sheet and save it to
'a temporary file
    Sheet2.Select
    ActiveSheet.Copy
    
'Copy and past special values to remove formulas and reduce file size
    
    Cells.Select
    Selection.Copy
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     
'filename

    Set WB = ActiveWorkbook
    FileName = "filename.csv"
    On Error Resume Next
        Kill FileName
    On Error GoTo 0
    WB.SaveAs FileName:=FileName
'Create and show the outlook mail item
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    With oMail
        'Uncomment the line below to hard code a recipient
        '.To = "someone@somedomain.com"
        'Uncomment the line below to hard code a subject
        .Subject = "somesubject"
        .Attachments.Add WB.FullName
        '.Body = "If you have any issues please don't hesitate to contact me"
        .Display
    End With
    
'Delete the temporary file
    WB.ChangeFileAccess Mode:=xlReadOnly
    Kill WB.FullName
    WB.Close SaveChanges:=False
  
'Restore screen updating and release Outlook
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
    
mailerr:
End Sub
I've been attempting to change this bit of code to do it but can't work it out:

WB.SaveAs FileName:=FileName

Can someone help!

Thanks a lot
Luke.