Free Microsoft Excel 2013 Quick Reference

Macro to create CSV files from Excel Spreadsheet

Hello,

How do I create VBA macro to create CSV files out of Excel spread sheet for each tab? Right now I am manually opening the spreadsheet and saving each tab as CSV file. But it is taking lot of time if we have lot of tabs.

Any help would be appreaciated !!

Thanks,
/Sukanya


Post your answer or comment

comments powered by Disqus
Hi,

When I was running the macro to create CSV files from excel spreadsheet, I came across new issue. It used to work fine earlier. There are extra spaces in cell. When i just try =trim(a1), it is removing the extra spaces and working fine. But when I put it in macro and run it, it is giving an error.

I have the below text in cell. I put HTML tags around text. Otherwise it is removing extra spaces. I have also attached speadsheet with this text.

HTML Code: 


Sub SaveAllSheetsAsCSV()
    Dim wks As Worksheet
    Dim strFileFullName As String, strFileName As String, strCSVname As String, csvfile As String, lngRows As Long, lngCols
As Long
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    
    'save the originating file name and path
    strFileFullName = ThisWorkbook.FullName
    'save the originating file name without type
    strFileName = Left(ThisWorkbook.Name, InStr(1, ThisWorkbook.Name, ".") - 1)
    
    'convert all sheets in the originating file as csv files
    'N.B. don't convert "RevisionHistory"
    For Each wks In ThisWorkbook.Worksheets()
        If wks.Name = "DatasetMetadata" Then
          csvfile = "dsmeta"
        ElseIf wks.Name = "ValueLevel" Then
                csvfile = "vlmeta"
        ElseIf wks.Name = "ComputationalAlgorithms" Then
                csvfile = "cameta"
        ElseIf wks.Name = "ExternalControlledTerminology" Then
                csvfile = "ectmeta"
        ElseIf wks.Name = "ControlledTerminology" Then
                csvfile = "ctmeta"
        Else: csvfile = wks.Name
        End If
        
        ' Remove Carriage Retruns
         For lngRows = 1 To 100
            For lngCols = 1 To 20
                If Len(wks.Cells(lngRows, lngCols)) > 0 Then
                      If (InStr(1, wks.Cells(lngRows, lngCols), vbCrLf, vbTextCompare) > 0) Or _
                      (InStr(1, wks.Cells(lngRows, lngCols), vbLf, vbTextCompare) > 0) Then
                          wks.Cells(lngRows, lngCols) = Replace(wks.Cells(lngRows, lngCols), vbCrLf, "")
                       wks.Cells(lngRows, lngCols) = Replace(wks.Cells(lngRows, lngCols), vbLf, "")
                     End If
               End If
          Next lngCols
        Next lngRows
         

        ' Remove Extra spaces
       For lngRows = 1 To 100
           For lngCols = 1 To 20
            If Len(wks.Cells(lngRows, lngCols)) > 0 Then
                wks.Cells(lngRows, lngCols) = WorksheetFunction.Trim(wks.Cells(lngRows, lngCols))
              End If
        Next lngCols
      Next lngRows

        
        If wks.Name <> "RevisionHistory" Then
              strCSVname = ActiveWorkbook.Path & _
                     "" & LCase(csvfile) & ".csv"
            wks.SaveAs Filename:=strCSVname, FileFormat:=xlCSV, CreateBackup:=False
        End If
    Next
    
    
    'Reopen the original Excel file
    Workbooks.Open strFileFullName
    
    Rem: Always set alerts and screen updating back to true
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    
    'Close the last csv file showing in Excel window
    ThisWorkbook.Close SaveChanges:=False

End Sub
Does any one have any idea why it is not working.

Thanks a lot for your help.

Hello,

I am trying to create CSV files from Excel spreadsheet for each tab. But when I convert these CSV files, text in cell is limiting to 255 characters, in otherwords it is getting truncated after 255characters. I am using Excel 2003 version.

And also when convereted these windows CSV files to UNIX csv files using the command "DOS2UNIX", some charaters becoming special characters. For exmaple, I have the actual text as "If AEENDTC ne '' then do; " and is becoming "If AEENDTC ne ćĆ then do; " Do you have any idea why it is doing this.

Thanks a lot for your help..
Sukanya

Sub CreateCSVFiles()
Application.DisplayAlerts = False

  Dim CSVName As String
  Dim CSVPath As String
  Dim NewWkb As Workbook
  Dim Wks As Worksheet
  
    CSVPath = "C:Documents and Settingsenugusxxxtest1"
    
      For Each Wks In ThisWorkbook.Worksheets
        Wks.Copy
        Set NewWkb = ActiveWorkbook
        CSVName = LCase(Wks.Name)
        NewWkb.SaveAs CSVPath & CSVName, xlCSV
        NewWkb.Close False
        Next Wks
      
End Sub


Hi there,

I am trying to write a macro to create CSV files from excel that are used to import into a workforce management application.
the CSV files are of different lenghts but always 8 columns long

I have got as far as sucsessfully creating the import document in the correct format but it is creating an extra line at the bottom of each CSV file that means at the moment i have to open each of them with notepad and delete the extra line before they are formatted correctly.

I believe there is a simple way of removing this last carraige return as i save the document but at present its driving me mad

any ideas?

cheers,

B_F

I want to write a macro that saves an .xls file to a .csv file by clicking the SAVE button on the toolbar in Excel.

I think it has something to do with the before_save event handler...

does anyone know the code i can use?

I'm looking to create a new file from data in my table. I don't want to even imagine having to do this manually again...I'm optimistic there is a solution. All the data needed to create the file is in the table, but i need it stacked and organized in a weird way. It's almost to hard to explain...so I color coded an attachement that basically says it all. It's pretty much the same thing repeated over and over except the last 2 lines. It's just a really messed up organization.
In the real version I need the new file in a new workbook.
I'm extremely grateful to anyone who can automate this thing

Thank you.

Hi All,

How to Create Database Tables from Excel Spreadsheets (of course thy should have unique titles as fields, rows as records … what else?) & use them in queries (like from Access tables)

Thank you in advance

I need to make .txt file from Excell table with specific file structure. How to easy do that?

Hallo,
I have a problem, I need to create the hyperlink from Excel doc. to the
folder, but not to the existing document, is there any way to do it?
Thank you

Is it possilble to hyperlink to a PDF file from Excel, and if so, how do you
do it?

Thanks,
Jay

Hi

I have a spreadsheet with a few sheets in it.

The basic info i am asked to enter is Credit or Debit and a description. The ultimate result has concatenated formulas to arrive to the output information.

The output file will look at about 25 records.
So in the end i will have a couple of header records and a couple of trailer records , plus the transactions in the middle.

My column looks like this.

01,CBA,FRANCOTY,200101,0200,1,,,2/
02,,CBA,1,090427,,AUD,2/
03,243810057822,,015,53214632,,,100,6365462,2,,400,600000,1,,900,000,,,901,000,,,902,000,,,903,000,, ,904,,,,905,,,/
16,399,50000,,MIS,apples,/
16,699,600000,,MIS,mangoes,/
16,399,6315462,,MIS,boris,/

49,67145556,5/
98,67145556,7/
99,67145556,1,9/

I have a macro that can remove the spaces , however my pretty basic vb code will still read all the lines and output to the file. When the lines are pushed up, my output file will still have the 32 lines including the blank one , which means my file will not load into another program.

Below is the code. Any tips of tweaking so i today i have 10 lines only 10 lines go to the output file. Tomorrow if i have 15 lines then 15 lines go to the output file.

'Upon clicking "Create BAI2 File" the following is executed

 Sub Macro1()
 
' Display "are you sure?" message.
Dim intResponse As Integer
intResponse = MsgBox("Click Yes to Proceed to the Creation of your Statement Data File", vbYesNo)
If intResponse = vbYes Then

' Option YES selected.
' Macro to create BAI2 file from Excel Spreadsheet

' Field name are variables
   Dim Line1, Line2, Line3, Line4, Line5, Line6, Line7, Line8, Line9, Line10, Line11, Line12, Line13, Line14, Line15, Line16,
Line17, Line18, Line19, Line20, Line21, Line22, Line23, Line24, Line25, Line26, Line27, Line28, Line29, Line30, Line31,
Line32
             
' check details
    Worksheets("Final").Activate

' Create the output file
    Close
    Filepath = (Cells(1, 3))
    Open Filepath For Output As #1
        
' Set up counter
'   For DESC = Cells(1, 1) To (Cells(1, 2))
   
' Make sure "Final" is the active Worksheet
    Worksheets("Final").Activate

' Get the fields
     Line1 = (Cells(1, 1))
     Line2 = (Cells(2, 1))
     Line3 = (Cells(3, 1))
     Line4 = (Cells(4, 1))
     Line5 = (Cells(5, 1))
     Line6 = (Cells(6, 1))
     Line7 = (Cells(7, 1))
     Line8 = (Cells(8, 1))
     Line9 = (Cells(9, 1))
     Line10 = (Cells(10, 1))
     Line11 = (Cells(11, 1))
     Line12 = (Cells(12, 1))
     Line13 = (Cells(13, 1))
     Line14 = (Cells(14, 1))
     Line15 = (Cells(15, 1))
     Line16 = (Cells(16, 1))
     Line17 = (Cells(17, 1))
     Line18 = (Cells(18, 1))
     Line19 = (Cells(19, 1))
     Line20 = (Cells(20, 1))
     Line21 = (Cells(21, 1))
     Line22 = (Cells(22, 1))
     Line23 = (Cells(23, 1))
     Line24 = (Cells(24, 1))
     Line25 = (Cells(25, 1))
     Line26 = (Cells(26, 1))
     Line27 = (Cells(27, 1))
     Line28 = (Cells(28, 1))
     Line29 = (Cells(29, 1))
     Line30 = (Cells(30, 1))
     Line31 = (Cells(31, 1))
     Line32 = (Cells(32, 1))
    
    
    
   
' Will output all neccessary fields for BAI2 Date file
 Print #1, Line1
 Print #1, Line2
 Print #1, Line3
 Print #1, Line4
 Print #1, Line5
 Print #1, Line6
 Print #1, Line7
 Print #1, Line8
 Print #1, Line9
 Print #1, Line10
 Print #1, Line11
 Print #1, Line12
 Print #1, Line13
 Print #1, Line14
 Print #1, Line15
 Print #1, Line16
 Print #1, Line17
 Print #1, Line18
 Print #1, Line19
 Print #1, Line20
 Print #1, Line21
 Print #1, Line22
 Print #1, Line23
 Print #1, Line24
 Print #1, Line25
 Print #1, Line26
 Print #1, Line27
 Print #1, Line28
 Print #1, Line29
 Print #1, Line30
 Print #1, Line31
 Print #1, Line32
 
 
'   Next DESC

' Close the file

    Close
   MsgBox "BAI2 Statement Data File created under specified folder" + Chr(13) + Chr(13) + Filepath, vbOKOnly +
vbInformation, "BAI2 Statement Data File created!"
      
 ' Option NO selected
   Else
   MsgBox "Aborted!", vbOKOnly + vbCritical, "File Creation Aborted!"
   
   
   
End If

End Sub

Thanks.

Hello! I am in the process of developing a quality control spreadsheet based
on CSV files that are exported periodically from a scientific instrument. The
CSV file always goes to the same place on the hard drive. For example,
C:ICAPQAQC

I need help on creating a macro button in the Excel 2007 workbook I'm
working on to automatically go to that directory and pull in any CSV file it
finds there. Once it imports the CSV as a sheet in the workbook, then I can
tell it to add the data from the sheet onto a master sheet for charting
purposes. But I do need some help creating a macro button to automatically
pull the CSV in.

Any ideas would be appreciated! Thanks!

I am trying to create code that will extract a unique file name from excel and create a generic sub folder under each unique folder.

Example:
Unique Folder
- Project A
Sub Folder structure
- Initiation Phase
- Definition Phase
- Execution Phase
- Close Out Phase

I have found and adapted the below code from another post on this forum to create the unique folder but am unable to code the generic folder creation part

Any assistance in this development is appreciated.

Sub CreateFolders()
   Dim rCell As Range
   Dim sFolder
   
   For Each rCell In Range("a1:a300")
      If Not rCell.Value = "" Then
         sFolder = "c:Data" & rCell.Value
         If Dir(sFolder, vbDirectory) = "" Then
            MkDir (sFolder)
         End If
      End If
   Next rCell
End Sub


Hi all -

confused noob here i want to create a function in excel to open a csv file on the web and then do copy and paste. so far so good until my company decided to put password on the link for security reason. my current script (without password) looks like this:

Workbooks.Open Filename:="http://www.ourwebsite.com/folder/samplefile.csv"

it works without password. the question is: how do i script the macro to open this csv file if the link is password protected?

thanks a bunch!
Andy

I am working on a workbook that imports data from a csv file on sheet 1 when clicking a macro button. (That is done)

On sheet 2 I have the data from sheet one displayed in a user friendly format. An the ability to override data. (That is done)

Then on sheet 3 I have the data from sheet 2 converted back to the format that sheet 1 is in.(That is done)

The last piece I cant figure out is how to make a macro that will parse through sheet 3 and overwrite the original csv file when clicking a button.

Sheet 3 data that needs to be extracted is A3:Y250.

This is an example of what one row in the csv file needs to look like:
0000 -Fee Desc-,0,-1,10,0,0,0,0,220,30,60,100,0,0,0,0,0,100,0,0,0,0,0,0,0

Any help would be greatly appreciated. Even if it isnt in a nice one click automated fashion. I just need to get this data into this format so I can upload it into an application and I am running out of time.

Thanks.

Hi there,
I need to create a Word document from Excel in which I would like to insert
the date/time and file name (insert Auto Text) in the header through VBA. In
fact, I can create the Word file from Excel but encounter error when it comes
to inserting date and file name in the header.
Appreciate if anyone is kind enough to help me out.

Thanks in advance for your kind assistance!
Regards,

Steve C.

Hi Guys,

I am new to this forum and this is my first post.

I have a requirement to develop a macro which should let me select multiple CSV files from my local drive and perform a match case on a particular column (say column AB) using the data on the macro file and if it's a match copy the next three column data from macro file to the selected CSV file.

Example:

Macro file: Sheet1 in workbook will have a command button which on click lets you select a CSV file. Sheet2 in the workbook will have the data as below.

Andy age23 football 3
Tommy age34 baseball 5
Rick age27 Actor 2
Sam age30 singer 6

CSV file: This file will have the names in column say AB, the macro file should do a match case similar to vlookup and if it finds a match move the next three column from the macro sheet2 to csv file on AC,AD and AE respectively.

Looking forward for your advice.

Regards,
Sinser

We use Excel 97 at our office on Windows XP Professional. A couple of users
want to be able to create new files by using new from the right click menu
and selecting Microsoft Excel Worksheet. The creation of the file works fine
in that the filename is filename.xls. When they open the file and add
information and then try to save the file, Excel wants to save as tab
delimited. Creating a file by opening Excel first works just fine. We have
tried this on several computers and the results are the same. For convenience
is there some way to change this behavior?

Hello,

I have written a short macros, which copy data range from Excel and paste it special as Enhanced Metafile in table cell in Word file.
The problem is that picture pasted is much larger than cell. But I need it fit the cell (cell size is fixed).
Maybe anybody had the same kind of a problem and can give an advice?

I used the code below. Maybe any is missing?

Selection.PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafile, Placement:=wdInLine, DisplayAsIcon:=False

Or give an advice how to resize pasted object.
Thanx a lot in advance!

Hi!
Searched a lot, but found no solution to my problem.
I am trying to use macro to import *.csv file data from internet using Data->Import extrenal data->import data. If I use macro recorder to record the procedure and use it again, it is not working. I know from somewhere that it is possible to download .csv file, save it and then import data using macro (I dont know exactly how, but there is possibility to do that). The question is - is it possible to import data using one macro and not saving file to the disc?
If needed, here is the link to CSV file:
"http://www.baltic.omxnordicexchange.com/market/?start_d=1&start_m=1&start_y=2000&end_d=24&end_m=7&end_y=2008&pg=nontradeddetails&instrument=LV00004 00323&tab=price&lang=lv&currency=0&date=&pg=nontradeddetails&pg2=funddetail&downloadcsv=1&csv_style= baltic".
Af course I need that data be delimited (using semicolon).

I will also use procedure that cheks in range A:A for different "instrument=LV0000400323" and changes the link, then creates (if its not already created) sheet and names it "instrument=LV0000400323", and copy data to that sheet... and so on. But for that I know solution... at least I think so

Thanx in advance!

I would like to create a macro that allows me to take data from one spreadsheet and populate various new sheets for billing purposes. For example, the data sheet includes fields for client name, billable account, billable value, etc. I would like to be able to fill in this data and then run a macro to create a new sheet (bill) for each client that plugs in values from the data sheet. I only know how to create a macro for one client at a time and then rename each sheet with the client short name (which is also a cell value on the sheet).

Here's the code, but I'm pretty certain it's a roundabout way of doing this:

Sheets("CLIENT TEMPLATE").Select
Sheets("CLIENT TEMPLATE").Copy
ActiveWorkbook.Colors = Workbooks("3rd Qtr 2006_1 - TEST.xls").Colors
Range("A2:H2").Select
ActiveCell.FormulaR1C1 = "='TEST DATA SHEET'!R[2]C[1]"
Range("F8").Select
ActiveCell.FormulaR1C1 = "='TEST DATA SHEET'!R[-4]C[-2]"
Range("F9").Select
ActiveCell.FormulaR1C1 = "='TEST DATA SHEET'!R[-5]C[-1]"
Range("C25").Select
ActiveCell.FormulaR1C1 = _
"=IF('TEST DATA SHEET'!R[-21]C[5]="""","""",'TEST DATA SHEET'!R[-21]C[5])"
Range("E25").Select
ActiveCell.FormulaR1C1 = _
"=IF('TEST DATA SHEET'!R[-21]C[4]="""","""",'TEST DATA SHEET'!R[-21]C[4])"
Range("F25").Select
ActiveCell.FormulaR1C1 = "='TEST DATA SHEET'!R[-21]C[1]"
Range("F29").Select
ActiveCell.FormulaR1C1 = "='TEST DATA SHEET'!R[-25]C[-3]"
Range("J1").Select
ActiveCell.FormulaR1C1 = "='TEST DATA SHEET'!R[3]C[-9]"

End Sub

Any help would be graetly appreciated. Thanks

Want to import a bunch of financial data that's in Excel into Quicken.
Can I create a QIF file from Excel?

hi all

there is Any chance to play a animated GIF file in EXCEL SPREADSHEET

I know about the Print# and the Input# and the Input# functions for Excel
when moving information to a text file from a spreadsheet. But, how do I set
it up so that I can concatanate string text(my own text) with the fields
coming from the spreadsheet? It is not going to be a a delimited file but a
custom format file that I want to blend in with my own stringed text. Please
help. Thanks!

Brett

Does anyone know the proper script to launch a RSF (Reflection) file from excel?

Thanks,
JM


No luck finding an answer? You could always try Google.