Saveas .csv |
Saveas .csv - Excel |
|
I have this code that is supposed to open excel file, manipulate them, then save the end result as a csv file. It doesn't...
Code:
Const pathPattern As String = "C:\*.xls*" fileName = Dir(pathPattern) Do While fileName "" Set wb = Workbooks.Open(fileName) With wb.Worksheets(1) 'Do something End With wb.SaveAs fileName:=wb.Path & "\" & fileName, FileFormat:=xlCSV, CreateBackup:=False wb.Close True fileName = Dir Loop
It always tries to replace the original excel sheet (exact name as original file). When I changed:
Code:
wb.SaveAs fileName:=wb.Path & "\" & fileName, FileFormat:=xlCSV, CreateBackup:=False
to
Code:
wb.SaveAs fileName:=wb.Path & "\" & fileName & ".csv", FileFormat:=xlCSV, CreateBackup:=False
then it kept the original file name (including the .xlsx) and added a .csv to the end.
Where am I going wrong???
AMAS
Similar Topics
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!
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..
Make sense? I'm a bit confused myself. Maybe just a list of basic workbook-switching techniques or commands would be useful.
- Michael
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 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
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
I need to open a password protected workbook using VBA.
I've tried the code below but I still get prompted for a password.
Code:
Workbooks.Open Filename:="\\HOME\Working\Report.xls" _ , Password:="xxxx"
How do I get it to open automatically???
Sub OpenPDF() 'Dim pdf As String On Error Resume Next 'pdf file to open pdf = "K:\PDF\mypdf.pdf" 'open the pdf file ActiveWorkbook.FollowHyperlink pdf End Sub
So then I tried to create an instance of acrobat by setting a reference to the acrobat object but I can't get this to work either!
The code I'm using is Code:
Sub OpenPDF() Dim pdf As AcroPDDoc Dim strPDF As String Set pdf = CreateObject("AcroExch.PDDoc") 'pdf file to open strPDF = "K:\PDF\mypdf.pdf" 'open the pdf file pdf.Open strPDF End Sub
Any ideas what could be wrong with either approach?
Thank-you
It does this using the indirect function because it needs to concatenate the
path and filename from other cells.
Problem is that if workbook B is closed, the cell in workbook A shows #REF!.
So can indirect work using closed external files or must the external files
be opened?
Is there a clever way of making that work?
Thanks!
The following code is placed in workbook 'A' and is used to open workbook 'B'. These workbooks will now always be housed in the same directory and i want to change the code to use a relative path reference by determining the path of workbook 'A'. here's what I had:
Sub income_statement()
Application.WindowState = xlMaximized
Application.Workbooks.Open "C:\Documents and Settings\.....\workbook B.xls", UpdateLinks:=xlUpdateLinksAlways
End Sub
could you suggest how to change this to use a relative path reference?
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
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
Any help is appreciated. Here is an excerpt of the code:
Workbooks.Open Filename:="C:\Profile.xls"
Sheets("SELECTION").Select
Range("F3").Value = SNR
' THIS PART ONLY WORKS IF YOU STEP THROUGH THE MACRO USING THE DEBUGGER...IF YOU RUN MACRO NORMALLY THE CONNECTIONS DO NOT REFRESH
ActiveWorkbook.Connections("Connection").Refresh
ActiveWorkbook.Connections("Connection14").Refresh
ActiveWorkbook.Connections("Query from C_Profile").Refresh
ActiveWorkbook.Connections("Query from C_Profile1").Refresh
My excel file will contain more than one tab.
I have to free the first two lines in all the tabs except the first one.
I tried the following code.
Code:
xlsobj.Worksheets(1).Rows("1:2").Select ActiveWindow.FreezePanes = True
But in this I have to change the Active Worksheets in VBA, Which I want to avoid.
Is there any method to achieve this without selecting the cells.
Something like
Code:
xlsobj.Worksheets(1).Rows("1:2").FreezePanes = True
Code:
Windows("File.xls").Activate
But what if the File.xls was a variable named File?
I have tried this:
Code:
Windows(File).Activate
Without success. Please advise.
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 written some code that when a button is selected will Refresh a Data Query Table and all of it's information. However, I seem to be getting issues, can some one point me in the right direction with the code?
Code:
.Sheets("Sheet 1").ListObject.QueryTable.Refresh BackgroundQuery:=False
When I recorded myslef doing this process it looked like this:
Code:
Sheets("Sheet 1").Select Range("D70872").Select Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Many thanks!
I'm new to macros and would appreciate any help this board can offer. Thanks...
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---
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.