Automatic save as for CSV

I have a list of csv files that I need to open re-format and then close the
file and re-name so that I can import into Access. (Access doesn't recogonize
the current name with dashes). I am manually doing a "save as" now. I was
also hoping to sequentially name the files for instance, logfile1, logfile2,
logfile3, logfile4 etc...   Can it be done? If I add it to the bottom of my
format macro it would all be in one step.

Thanks

-- 
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/201004/1

0
Carrie_Loos
4/29/2010 4:56:14 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
636 Views

Similar Articles

[PageSpeed] 34

I'm not sure how you have that list or what you do to open it, but this worked
ok for me with a single .CSV file.

Option Explicit
Sub testme()

    Dim myFileName As String
    Dim myPath As String
    Dim wks As Worksheet
    Dim TestStr As String
    Dim iCtr As Long
    Dim MaxTries As Long
    Dim NewFileName As String
    Dim NextFileName As String
    Dim UseThisOne As Long
    
    MaxTries = 99
    
    myPath = "C:\my documents\excel\"
    If Right(myPath, 1) <> "\" Then
        myPath = myPath & "\"
    End If
    
    myFileName = "Book1.csv"
    
    Workbooks.Open Filename:=myPath & myFileName
    Set wks = ActiveSheet
    
    With wks
        'do your manipulation here
        
        'remove the .csv from the original filename
        NewFileName = Replace(expression:=myFileName, _
                        Find:=".csv", _
                        Replace:="", _
                        compare:=vbTextCompare)                        
        
        'remove any dashes
        NewFileName = Replace(expression:=NewFileName, Find:="-", Replace:="")
        
        'look for the next available number
        UseThisOne = -1
        For iCtr = 1 To MaxTries
            TestStr = ""
            NextFileName = myPath & NewFileName & Format(iCtr, "00") & ".csv"
            On Error Resume Next
            TestStr = Dir(NextFileName)
            On Error GoTo 0
            If TestStr = "" Then
                UseThisOne = iCtr 'found it!
                Exit For
            End If
        Next iCtr
        
        If UseThisOne < 0 Then
            MsgBox "Error--ran out of numbers, file not saved!"
        Else
            .Parent.SaveAs Filename:=NextFileName, FileFormat:=xlCSV
        End If

        .Parent.Close savechanges:=False

    End With
    
End Sub

"Carrie_Loos via OfficeKB.com" wrote:
> 
> I have a list of csv files that I need to open re-format and then close the
> file and re-name so that I can import into Access. (Access doesn't recogonize
> the current name with dashes). I am manually doing a "save as" now. I was
> also hoping to sequentially name the files for instance, logfile1, logfile2,
> logfile3, logfile4 etc...   Can it be done? If I add it to the bottom of my
> format macro it would all be in one step.
> 
> Thanks
> 
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/ms-excel/201004/1

-- 


Dave Peterson
0
Dave
4/29/2010 9:39:36 PM
Awesome, you always come through! Thanks Dave

Dave Peterson wrote:
>I'm not sure how you have that list or what you do to open it, but this worked
>ok for me with a single .CSV file.
>
>Option Explicit
>Sub testme()
>
>    Dim myFileName As String
>    Dim myPath As String
>    Dim wks As Worksheet
>    Dim TestStr As String
>    Dim iCtr As Long
>    Dim MaxTries As Long
>    Dim NewFileName As String
>    Dim NextFileName As String
>    Dim UseThisOne As Long
>    
>    MaxTries = 99
>    
>    myPath = "C:\my documents\excel\"
>    If Right(myPath, 1) <> "\" Then
>        myPath = myPath & "\"
>    End If
>    
>    myFileName = "Book1.csv"
>    
>    Workbooks.Open Filename:=myPath & myFileName
>    Set wks = ActiveSheet
>    
>    With wks
>        'do your manipulation here
>        
>        'remove the .csv from the original filename
>        NewFileName = Replace(expression:=myFileName, _
>                        Find:=".csv", _
>                        Replace:="", _
>                        compare:=vbTextCompare)                        
>        
>        'remove any dashes
>        NewFileName = Replace(expression:=NewFileName, Find:="-", Replace:="")
>        
>        'look for the next available number
>        UseThisOne = -1
>        For iCtr = 1 To MaxTries
>            TestStr = ""
>            NextFileName = myPath & NewFileName & Format(iCtr, "00") & ".csv"
>            On Error Resume Next
>            TestStr = Dir(NextFileName)
>            On Error GoTo 0
>            If TestStr = "" Then
>                UseThisOne = iCtr 'found it!
>                Exit For
>            End If
>        Next iCtr
>        
>        If UseThisOne < 0 Then
>            MsgBox "Error--ran out of numbers, file not saved!"
>        Else
>            .Parent.SaveAs Filename:=NextFileName, FileFormat:=xlCSV
>        End If
>
>        .Parent.Close savechanges:=False
>
>    End With
>    
>End Sub
>
>> I have a list of csv files that I need to open re-format and then close the
>> file and re-name so that I can import into Access. (Access doesn't recogonize
>[quoted text clipped - 8 lines]
>> Message posted via OfficeKB.com
>> http://www.officekb.com/Uwe/Forums.aspx/ms-excel/201004/1
>

-- 
Message posted via http://www.officekb.com

0
Carrie_Loos
4/29/2010 10:44:57 PM
Reply:

Similar Artilces:

line down page when saved as tiff
It's not there in the publisher file, but prints a line when saved as a tiff. What's going on? Also, what do I need to do to be able to save publisher files as PDFs? Thanks in advance RV <RV@discussions.microsoft.com> was very recently heard to utter: > Also, what do I need to do to be able to save publisher files as PDFs? Download and install a PDF converter such as the free PrimoPDF from www.primopdf.com Simply print to the PDF printer driver, and PDF is created from any application. -- Ed Bennett - MVP Microsoft Publisher Thanks, I'll check it out. One woul...

Saving only updated cells or worksheets
(This is the first time I'm using this feature - hopefully I'm following the proper procedure!) Is there a way, in Excel 2002, to only save the cells or worksheets that have been updated, instead of saving the entire file each time? I have a large file, with VBA code, and is I could set it to only save the updated cells or worksheets, this would decrease the time needed to save the file. I read about Tools, Options and Allow Fast Saves, but when I follow that path, there is no such option to Allow Fast Saves. Any assistance would be greatly appreciated! Thank You, MWS I am ...

Deleted Items
Hi there. I want to let messages unread that I keep (so I leave any "automatically mark message as read" option off)... yet I'd like to delete messages from my Inbox and have them get marked as "read" in the Deleted Items folder. Any add-in's that would handle this? Alex Alex Calder <helispot98@hotmail.com> wrote: > I want to let messages unread that I keep (so I leave any > "automatically mark message as read" option off)... yet I'd like to > delete messages from my Inbox and have them get marked as "read" in > the...

AUTOMATICALLY ALLOWS EACH ACCOUNT USER TO WRITE AN EMAIL WITH THE.
I have multiple accounts set up in Outlook, and we used to be able to write emails without having to change the accounts manually when using the 'non-default' account. I find it inconvinient that each time, when using the 'non-default' account, i have to remember to manually change the account first, to avoid the embarrasement of using my other family member's account to write a personal message. Can you bring back the automatic account change so that each time when we open each account, we are naturally prompt to the corresponding email account when we write a mes...

How do I keep Outlook from automatically downloading email
I want to open outlook but not have it automatically go and get my email until I tell it to. What version of Outlook are you using? In OL2002 & OL2003 you can control that here: Tools menu > Send/Receive > Send/Receive Settings > Define Send/Receive Groups "Marseldad" <Marseldad@discussions.microsoft.com> wrote in message news:4CA6667C-D50A-4CE1-9E9E-6E8587468C4E@microsoft.com... >I want to open outlook but not have it automatically go and get my email > until I tell it to. Maybe go to 'file', then 'work off line'? Then you have to ...

automatic data fill , based on criteria into dynamic range
Hi I'm tring to build a dynamic spreadsheet for forecast modelling over differering future periods where the end user chooses the periods to be used... ok this is what i am trying to do......its about defeated me.after 5 hours of trying -maybe i need to go on a advanced course or vba.... i have a list of periods that are in a single column list on Sheet1 and run from p1-2004 to p13-2034 (yep 30 yrs!) Sheet2 - has two list boxes: 1st list box - is where end user chooses 'start period' eg 'p11-2004' ( filled in using the data validation option from the excel menu t...

Word Count when saving large documents
On some large documents, but not all, there is a verrrry long delay when saving the document as it repeatedly recalculates the number of words in the document. Is there a way to turn off the Word Count function in such documents?? Thanks. -- Cyndie Browning Software Support Specialist GableGotwals Tulsa, OK Hi Cyndie, Getting the word count is almost instantaneous - and doesn't rely on the save process for an update. If you're working over a network, the *network* may be the cause of the poor performance. -- Cheers macropod [Microsoft MVP - Word] "...

how do i save excel workbook every 30 seconds.
I need a macro or function which open and save excel workbook every 30 seconds. ...

ftp server or I can saved from Ecxel? #2
I need to fine out how to save a hml workbook onto my website do I need to use ftp server or I can saved from Ecxel? Kosta ...

Losing Saved data
Excel 2000, Just in last two day i haved been losing the information in my saved workbooks. I save file and can close it and reopen the info is saved, but if i close excel and reopen it the info i just saved is gone and it is back to the info before i saved it. I have used this program for over 3 years and never had a problem. Could this be a virus? Verify that you are opening the same file in the exact same location that you saved the file in "Brian Ramsey" <anonymous@discussions.microsoft.com> wrote in message news:0d6a01c49c0f$7cdf7bc0$a301280a@phx.gbl... : Excel ...

Automatic Text Labels in Charts
Hi folks, I need to call upon the experts here for a problem that has left me scratching my head. I have opened an existing Excel file that has a line chart with 3 data series and is plotted by date on the X axis. The originator of the chart has accessed Data Labels for one of the data series, which allows text input at that line - and can be a different value (or blank) for each data point in the series. The Format Data Series Window - Data Labels tab shows the box "Automatic Text" selected for this series. I need to do a the same thing in a new Excel file, but when...

Automatic Graph generation by series
In my work book I have a sheet which contains Scheduled installations and successful installations per week. Is there a way to automatically generate a graph depending on the week number? -- SiliconAlleyDude Yes, you can do so by creating dynamic formulas to control the series. You can find an explanation here. http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=518 ---- Regards, John Mansfield http://www.pdbook.com "SiliconAlleyDude" wrote: > In my work book I have a sheet which contains Scheduled installations and > successful installations per week. &g...

Automatically update an AmendDate field on my form using VBA
Please Help! Am trying to get a form to automatically populate an amend date field upon any change to the record. I've tried the following in the form level Form_AfterUpdate Event Private Sub Form_AfterUpdate Me.IncidentAmendDate = Date End Sub This correctly populates the field on the form, but then won't let me move to any other record, exit the record etc unless I dont save. Please advise!!! Try moving the code to the form's before update event Private Sub Form_BeforeUpdate Me.IncidentAmendDate = Date End Sub John Spencer Access MVP 200...

Saving sent messages together with original blocks outbox
When I check the advanced option "In folders other than the Inbox save replies with the original" all outgoing emails get stuck in the outbox and won't send. Anyone experience this problem and have solutions ? ...

how do I turn off an Automatic Scientific Format
Every Time i want to Enter some Text in to Excel it is automaticall formatted as Scientific Text .. IE: I enter in 5E1 amd Excel Automatically formats the number an displays it as 5.00E+1 (50) and so on and so on for other code further down that are 5E4, 5E5 etc ... am I able to turn off thi Automatic Scientific Function that deems my codes are Exponentia Formulas -- Message posted from http://www.ExcelForum.com Hi not sure you can turn it off, but if you enter an apostraphe in front of the values it won't think they're numbers.. e.g. '5E1 alternatively, select where you a...

Excel
I am using CSV format in Excel and need to be able to define different column widths. Is there no way to do this ? If there isnt a way, is there any other text file format I can use to store this information in ? Kevin. Comma separated values files usually contain fields that vary in length (between records). The comma is the delimited that tells the program reading the file what goes in each field. If you want fixed width fields (like for a mainframe application????), you may be able to just adjust the columnwidths and do File|saveAs (and choose "formatted text (space delimited) (...

How can I automatically color a row based on cell content
I'd like to automatically color code an entire row of a spreadsheet I'm creating based on the entry of a single cell. The cells with the entries are all in the same column in no particular order. Example, type in 'SS' turns the row red, 'ST' turns it green. Thank You Ed Select the row. Format>Conditional Formatting>Formula is: =$A1="SS" format to red Add>Condtion2 =$A1="ST" format to green. Excel 2007 very similar except uses "rules" instead of "conditions" Gord Dibben MS Excel MVP ...

Saving data from webpage to excel
Hi, I was wondering is there any way that data from a web front end can be saved directly to an excel spreadsheet, instead of to a database. If so, can the same security features be incorporated? Thank You Kristie ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements ...

Automatic naming of files
Is it possible to automatically assign the text in a particular spreadsheet cell to the file name when Saving or Saving As? Hi Alan, Yes it is with VBA, for example Activeworkbook.SaveAs Filename:=Activesheet.range("A1") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Alan Dresch" <anonymous@discussions.microsoft.com> wrote in message news:9b0301c3eb08$1de1f840$a001280a@phx.gbl... > Is it possible to automatically assign the text in a > particular spreadsheet ce...

How do I update Outlook Tasks automatically from Excel.
When I open up the Task folder in Outlook, I would like for new tasks to be inserted automatically from an Excel file created via Access. Performing the manual Import function from the Excel file to Tasks works but I would hope to be able to perform the insert programmatically. You may want to look at http://www.outlookcode.com for some potential code samples to automate this process. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft.com/KB/555375 ...

Birthday Greeting automatic send email
Does anyone know howto automatic send birthday greeting email to the customers which is in MS CRM database? Cheers, Licky Technical Consultant PT. Mitrasoft Infonet Indonesia Licky, Use the SDK to retrieve a list of contacts with Birthday's today and then send an email. Run the application as a Windows task. Stephen "Licky Suryawan" <licky@mitrasoft.co.id> wrote in message news:O0BKty5mEHA.3196@TK2MSFTNGP10.phx.gbl... > Does anyone know howto automatic send birthday greeting email to the > customers which is in MS CRM database? > > Cheers, > &g...

PlanPlus saving / backup Goals?...
I am using Franklin Covey's PlanPlus for Outlook. I have goals set and I check them off, I would like to be able to either have them "disappear" when checked or at least move them off the list and back them up monthly or every 6 months. Tech support at FC says I need to delete the completed goals manually and they are not backed up anywhere they are deleted permenantly. Seems very basic for such a nicely designed system. I know Outlook can be set up to make tasks on the task list disappear when checked (they are still there, the program just does not show completed goals). Is t...

Automatic update of graph for 13 weeks
Hi, I have a worksheet with 52 work weeks on it. Each week I enter in new data and I want my graph to update for the latest 13weeks.... Does anyone know how to do this?? Hi, Have a look at the technique described by Jon Peltier, which you should be able to adapt for your data set are required period numbers. http://peltiertech.com/Excel/Charts/DynamicLast12.html Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "mursin" <u35883@uwe> wrote in message news:7540e5778c97a@uwe... > Hi, > > I have a worksheet with 52 work weeks on it. Each wee...

Password reset is not saved
I am using Vista Home Premium (32-bit) in a guest operating system (VMware; host is Vista 64-bit). I am receiving a Windows notification that my password is about to expire. Even though I change and save the password successfully, the next time that I log on I receive the same message. And , I have to go back to the original password, since the one that I changed to is not recognized. Any thoughts? Gordon Biggar On Thu, 3 Dec 2009 16:27:15 -0600, Gordon Biggar wrote: > I am using Vista Home Premium (32-bit) in a guest operating system (VMware; > host is Vist...

Workbook is taking 2
I have a workbook that contains 1,000 rows. The workbook has months accross (from Jan-01 to present). There are 'Sumif' formulas in every cell for every month. When the workbook was first created it would save on demand. However, the file now takes approximately 2 - 3 minutes to save. I suspect the numerous 'Sumif' formulas carry a lot of overhead. What can I do to get it to save faster. Note: The Sumif formulas are linking to data in another workbook ...