473,320 Members | 1,951 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
3 4307
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
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
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

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

Similar topics

2
by: Hon Seng Phuah | last post by:
Hi all, I have a huge excel format file wants to export to sql server database. One of the field has combination of numeric and alphanumeric. When I import the excel format to sql server...
7
by: MLH | last post by:
A97 has menu options that support exporting table data to ms excel data file format. Is this easily implemented from within code? Any examples? I looked in A97 HELP for the TransferSpreadsheet...
3
by: ABC | last post by:
What methods can export excel file from stored procedure with parameters which input from web form?
4
by: Juliano.net | last post by:
How can I read an Excel 2003 XML Spreadsheet and save it as an Excel 97 file?
1
by: panku007 | last post by:
hi All, I wanted to export excel data into ms word document through java coding.So please send me reply,according to my question. thanks
3
by: vedika | last post by:
hi, I created one application in which there is export functionality. I am using sqlCE as database . When user click on "export" a save file dialog appeare and file saved in html format from...
1
by: CF FAN | last post by:
Can Report Builder Export Excel Files with multiple Sheets How can I create a report that it can export in excel by multiple
3
by: minhtran | last post by:
Hi all Anyone has any idea how to get a Excel header column in VB.NET. Please, help me. Thank you so much in advance
0
by: shyshack | last post by:
Hi, I am quite new in C#. I am writing an application in which I have to open Excel file to import data from it. When opening small files everything works ok - the problem appears when I try to...
3
by: prashantdixit | last post by:
Hi, I am trying to export excel data to text file in a particular format. The Format for each ROW of Excel is like IMAN_ROOT/bin/import_file -f=<Column A> -type=<Column B> -d=<Column C>...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.