By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,611 Members | 1,541 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,611 IT Pros & Developers. It's quick & easy.

How to export a row from Excel and save as a new CSV file?

P: 8
I am trying to export a row from an excel sheet and then save it as a new CSV file but I have hit a
giant wall, any help would be highly appreciated.
This is the flow of my program.
First of all when the save button is clicked some data from a form is saved on the excel sheet since there is
need for users to correct or add on the saved data.
I want users to save and close the workbook.
And also want to save the saved row as a CSV file
only after users have made changes to the saved excel
data.
I don't know where and when to carry out the saving to
a CSV file. Users don't have to be aware of the saving
to csv file.
Below is a portion of the code
The copy and paste of the row doesn't take place what
can I be doing wrong ?

Expand|Select|Wrap|Line Numbers
  1.  Dim xlapp As Excel.Application
  2.  Dim xlbook As Excel.Workbook
  3.  Dim xlsheet As Excel.Worksheet
  4.  Dim SheetName As String
  5.  Dim i As Long
  6.  Dim RSEQ As Integer
  7.  Dim LastRow As Long
  8.  Dim LastColumn As Integer
  9.  Dim strFound As String
  10.  Dim RowNum As Long
  11.  Set xlapp = New Excel.Application
  12.  
  13.    'Open the file
  14.  
  15.    With xlapp
  16.  
  17.          xlapp.Visible = True
  18.  
  19.          Set xlbook = .Workbooks.Open("C:\Drawings_CSV_Spec.xls")                                    
  20.  
  21.          SheetName = "specialEstimate"
  22.  
  23.          Set xlsheet = .Worksheets("specialEstimate")                                                   
  24.  
  25.          'find Last Row & Column
  26.          LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row                      
  27.          LastColumn = xlsheet.Cells.Find(What:="*", After:=xlsheet.Range("A1"), _
  28.          SearchOrder:=xlByColumns, _
  29.          SearchDirection:=xlPrevious).Column
  30.          'unhide all hidden columns        
  31.          For i = 1 To LastColumn
  32.              If Columns(i).EntireColumn.Hidden = True Then
  33.                  Columns(i).EntireColumn.Hidden = False
  34.              End If
  35.          Next i
  36.  
  37.          T_EstimateNum = S_EstimateNum      'S_EstimateNum created by access estimation prog
  38.  
  39.          '------------------------------------------------------------------------------------------------
  40.          'Find row number of Estimate Number  
  41.  
  42.          strFound = Cells.Find(What:=T_EstimateNum, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
  43.          xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
  44.  
  45.          ' Write data to Excel sheet here 
  46.  
  47.          'Store row number in Variable
  48.          RowNum = ActiveCell.Row
  49.  
  50.          xlsheet.Range("A" & RowNum & ":AH" & RowNum).Select
  51.  
  52.          xlsheet.Columns("C").EntireColumn.Hidden = True
  53.          xlsheet.Columns("D").EntireColumn.Hidden = True
  54.          xlsheet.Columns("E").EntireColumn.Hidden = True
  55.          xlsheet.Columns("F").EntireColumn.Hidden = True
  56.          xlsheet.Columns("G").EntireColumn.Hidden = True
  57.          xlsheet.Columns("O").EntireColumn.Hidden = True
  58.          xlsheet.Columns("R").EntireColumn.Hidden = True                  
  59.          .Visible = True
  60.          xlsheet.Select         
  61.     End With
  62.     ' Below selected range    E.g A1459:AH1459 is saved as a CSV file
  63.     ' T_EstimateNum is used as file Name
  64.  
  65.      strFound = Cells.Find(What:=T_EstimateNum, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
  66.      XlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
  67.  
  68.      xlsheet.Range("A" & RowNum & ":AH" & RowNum).Select
  69.      Selection.Copy
  70.      Workbooks.Add
  71.      ActiveSheet.Paste
  72.  
  73.      xlbook.Application.DisplayAlerts = False
  74.  
  75.      ActiveWorkbook.SaveAs FileName:= _
  76.      "C:\Documents and Settings\designsection\My Documents\" & T_EstimateNum & ".csv" _
  77.      , FileFormat:=xlCSV, CreateBackup:=False
  78.  
  79.      ActiveWorkbook.Close
  80.      Xlbook.Application.DisplayAlerts = True
  81.  
  82.  
Nov 5 '11 #1
Share this Question
Share on Google+
3 Replies


P: 27
Put an own Save Button on your worksheet. Write event handlers which disable the ordinary Save menu point of the main menu and the Ctrl-S key combination or redirect them to the click event handler of your own Save button.
Define a flag for usage in the event handler of closing with false initial value.
In this event handler
1. Display a common dialog where the user can enter the name of the CSV file.
2. Open the selected file for writing.
3. Identify somehow the cells to be save in CSV format.
4. For each row do:
4.1. Concatenate the cells separated with commas into one string.
4.2. Write the string to the selected file terminated by newline.
5. Close the file.
6. Set your "saved" flag to "true".

Hint:
I had a similar problem when I had to pass an data from an Excel worksheet to a text file having fixed field widths of 12 characters used by Fortran Read statements.
I truncated or padded each cells to exactly 12 characters and concatenated them without separators.

http://users.atw.hu/franknagy/Tipp/Macros/12x12.htm
Nov 6 '11 #2

P: 8
Thanks Frank for the prompt response.
I can't place code on the excel sheet
because the Excel workbook is used online by
alot of people and the data is usually input manually
and I don't have editing rights on the workbook.
I have been tasked with automating the transfer of data
from an access Estimation program that I am writing.
I am new at VBA and don't know how to go about with the automation.
Nov 6 '11 #3

P: 27
Massamiya,
Download my tutorial about macros in Word and Excel.

http://franknagy.atw.hu/Tipp/Macros/Macros_in_Microsoft_Excel_and_Word.ppt

Regards
Frank
Nov 7 '11 #4

Post your reply

Sign in to post your reply or Sign up for a free account.