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
ADVERTISEMENT
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
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
Jan 27, 2009
find attached an example of the spreadsheet I am working with. Please bear in mind that this is a much simplified version of the version I am currently working on (which needs to have 1000 lines). What I am trying to achieve is allow my team to enter rows of data into the spreadsheet in a format that they will be familiar with - then hit the button on the sheet which will then take a copy of the second sheet (which looks up against the first) and spit it out in a .txt file ready to be uploaded into our computer system.
The main priority that I need to fix is that when the .txt file is opened in notepad it contains a huge amount of blank data rows at the bottom - I assume that it is taking accross all 65536 lines into the .txt where I only want the rows that have data in them in the .txt. At present our computer system will not accept the .txt due to all the blank rows (its limit is 1000 lines).
View 5 Replies
View Related
Mar 11, 2014
am using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....
I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....
I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...
View 6 Replies
View Related
Jul 10, 2014
I have a Workbook that already has a macro in it that will generate multiple sheets based on certain criteria.
For each of these newly generated sheets (numbered 1-6 in the attached example), I need to be able save each of them to a PDF file based on a unique name contained in a certain cell (in this case, each named is referenced in cell Q1 of each sheet). As such, I should end up with 6 pdfs based on the attached sample files.
The PDFs would ideally need to be landscaped and should be just 1 page per sheet.
In my attached sample workbook, you'll notice that I have a "PracticePrint" macro that doesn't quite get the job done. One other criteria involves not printing any sheet with the characters "1010" in it (my PracticePrint macro is setup to do that, but it fails to do much else).
View 1 Replies
View Related
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
Jul 7, 2014
As a newbie to macros I am having trouble trying to save a worksheet to CSV file. The macro worked to .txt but when I change it to .xlCSV it comes up with runtime 1004 error, method 'save as' of object '_workbook' failed.
Sub Export7400_setup_Click()
'
' Exportsub Macro
'
Dim sPath As String
Dim FName As String
FName = Range("rng7400Filename")
sPath = Range("strWorksheetPath")
[Code]...
View 3 Replies
View Related
Oct 6, 2012
Ticker = "ABC"
I have made a url
[URL] ......
And try to open it
Workbooks.Open(url)
But I get the error
Object does not support this property or method
I am trying to download an xls file, copy the data to a worksheet with the name "ticker" and then close the xls download file
View 3 Replies
View Related
Feb 27, 2007
I have a workbook with multiple worksheets. I want to save only a specific worksheet to a new file name and have only the values in the work sheet saved. This particular worksheet references values on another worksheet which uses VLOOKUP to pull data from yet another worksheet. I also want to automatically name the file using a pre-determined name located in a cell reference and save it to a specific location on the LAN drive.
View 2 Replies
View Related
May 30, 2008
I am copying a sheet out to a new workbook in order to save as a CSV file. I was wondering if there was a better way of doing this than the code below which uses ActiveWorkbook to determine the newly copied sheet.
Sub test()
Dim OutputFile As Workbook, InputFile As Workbook
Dim sDD As Worksheet
Set InputFile = Workbooks.Open("H:TestTestInput.xls")
Set sDD = InputFile.Worksheets("Data Dump")
sDD.Copy
Set OutputFile = ActiveWorkbook
OutputFile.SaveAs Filename:="H:TestTestOutput.csv", FileFormat:=xlCSV
End Sub..................
View 2 Replies
View Related
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
Jan 21, 2010
I have this macro I recorded and pieced together from info I found on the net. It works the first time you run it but then it dies because the next new workbook is not "book1" what I would prefer is to have it save the new workbook as the value of sheet2 F2 and "Product Bulletin" so it would look like "##### Product Bulletin" but I don't know how to write it.
View 6 Replies
View Related
May 23, 2006
The idea was to save the the selected files specified in the array as individual files. For example, In the Workbook "Main", there are worksheets "Susan", "Mary" and "John". If i specify "Susan" and "Mary" in the array, it should output only these 2 individual files. However, I am only getting the file which is actively selcted in the main. Is there anything wrong with the for loop?
Sub SaveShtsAsBook()
Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&
Dim ws As Worksheet
MyFilePath$ = ActiveWorkbook.Path & "" & _
Left(ThisWorkbook. Name, Len(ThisWorkbook.Name) - 4)
With Application
. ScreenUpdating = False
.DisplayAlerts = False
' End With
On Error Resume Next '<< a folder exists
MkDir MyFilePath '<< create a folder
For Each ws In Worksheets(Array("Mary", "Susan"))
SheetName = ActiveSheet.Name
Cells.Copy..............................
View 2 Replies
View Related
May 14, 2008
I have a folder that has a bunch of text files in it with numeric names (they are store numbers 2, 3, 165, 188, etc...). I need to open those files (in excel), run a macro on them (this portion of the macro has already been created), and then save them with the same name as the txt file but in an xls format and close. It would be great to have the whole folder process automatically but I am willing to start small. Further, I'd like it to not ask for a filename, and I don't want to see the SaveAs dialog box. So far I have been able to get the macro to run through the my processing of the text file all the way to the SaveAs portion, but the code in my macro opens the SaveAs box and puts the filename of the txt file in the file name box in quotes with the txt extension. Below is the code.
Sub Macro3()
sFile = Application. GetOpenFilename( _
fileFilter:="Text Files (*.txt), *.txt", FilterIndex:=1, _
Title:="Open Workbook")
Workbooks.OpenText Filename:= _
sFile, Origin:= _
xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= Array(Array(0, _
1), Array(5, 1), Array(26, 1), Array(35, 1), Array(39, 1), Array(46, 1), Array(51, 1), Array _
(58, 1), Array(75, 1), Array(87, 1), Array(91, 1), Array(97, 1), Array(99, 1), Array(111, 1) _.....................
View 5 Replies
View Related
Jun 18, 2007
About 2 weeks ago Reafidy posted a reply to the following thread. Save Individual Sheet Based On A1. As I do not want to hi-jack anyone elses thread so I am starting a new one. The code posted does basicly what I need however I was wondering if it is possible to save only "ONE" specific sheet which could be specified in the code, to a specific folder specified in the code and under a name defined by a cells contents. eg.
Save a single sheet named "MatData" from a workbook, name the saved sheet from a "named range on sheet1" & "Cutlist" & ".xls"
to a Folder on the "D" drive called "Saves"
View 3 Replies
View Related
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
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
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
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
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
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
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
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
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
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
View Related
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
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
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
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
