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
- Dim xlapp As Excel.Application
- Dim xlbook As Excel.Workbook
- Dim xlsheet As Excel.Worksheet
- Dim SheetName As String
- Dim i As Long
- Dim RSEQ As Integer
- Dim LastRow As Long
- Dim LastColumn As Integer
- Dim strFound As String
- Dim RowNum As Long
- Set xlapp = New Excel.Application
- 'Open the file
- With xlapp
- xlapp.Visible = True
- Set xlbook = .Workbooks.Open("C:\Drawings_CSV_Spec.xls")
- SheetName = "specialEstimate"
- Set xlsheet = .Worksheets("specialEstimate")
- 'find Last Row & Column
- LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
- LastColumn = xlsheet.Cells.Find(What:="*", After:=xlsheet.Range("A1"), _
- SearchOrder:=xlByColumns, _
- SearchDirection:=xlPrevious).Column
- 'unhide all hidden columns
- For i = 1 To LastColumn
- If Columns(i).EntireColumn.Hidden = True Then
- Columns(i).EntireColumn.Hidden = False
- End If
- Next i
- T_EstimateNum = S_EstimateNum 'S_EstimateNum created by access estimation prog
- '------------------------------------------------------------------------------------------------
- 'Find row number of Estimate Number
- strFound = Cells.Find(What:=T_EstimateNum, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
- xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
- ' Write data to Excel sheet here
- 'Store row number in Variable
- RowNum = ActiveCell.Row
- xlsheet.Range("A" & RowNum & ":AH" & RowNum).Select
- xlsheet.Columns("C").EntireColumn.Hidden = True
- xlsheet.Columns("D").EntireColumn.Hidden = True
- xlsheet.Columns("E").EntireColumn.Hidden = True
- xlsheet.Columns("F").EntireColumn.Hidden = True
- xlsheet.Columns("G").EntireColumn.Hidden = True
- xlsheet.Columns("O").EntireColumn.Hidden = True
- xlsheet.Columns("R").EntireColumn.Hidden = True
- .Visible = True
- xlsheet.Select
- End With
- ' Below selected range E.g A1459:AH1459 is saved as a CSV file
- ' T_EstimateNum is used as file Name
- strFound = Cells.Find(What:=T_EstimateNum, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
- XlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
- xlsheet.Range("A" & RowNum & ":AH" & RowNum).Select
- Selection.Copy
- Workbooks.Add
- ActiveSheet.Paste
- xlbook.Application.DisplayAlerts = False
- ActiveWorkbook.SaveAs FileName:= _
- "C:\Documents and Settings\designsection\My Documents\" & T_EstimateNum & ".csv" _
- , FileFormat:=xlCSV, CreateBackup:=False
- ActiveWorkbook.Close
- Xlbook.Application.DisplayAlerts = True