lazyuser
02-18-2009, 02:36 PM
hi,
i have a vb form (within word). this form has four text fields and a button. user enters some info in these text fields and on button click, these values are added into four columns (C, D, E and H) at the very end of the spreadsheet - after the last populated line. the next time i run this, it should add the info into the four columns in the next line -after the previous additions.
this is the code i have:
Private Sub cmdAppend_Click()
Dim xlsWB1 As Object
Dim xlsWS1 As Object
Dim strFileName As String
Dim oRange As Range
'strFileName = "D:\xxxxxxx\POC\US Reporters_123.xls"
'Opening the file to parse now
Set xlsApp = CreateObject("Excel.Application")
xlsApp.Visible = True 'Makes Excel File visible.
Set xlsWB1 = xlsApp.Workbooks.Open(strFileName)
Set xlSheet = xlsWB1.Worksheets(1)
xlSheet.Activate
xlSheet.Range("A1").Select
Dim col As Integer
Dim row As Integer
Dim maxrow As Integer
Dim maxcol As Integer
Dim str As String
Dim rListPaste As Range
str = ""
maxrow = xlSheet.Range("A1").CurrentRegion.Rows.Count
maxcol = 9
'Reading the Excel file and putting everything in Memory for faster manipulation
row = maxrow + 1
xlSheet.Range("C" & row).Value = txtReporterName.Text
xlSheet.Range("D" & row).Value = txtPrimaryAbbrev.Text
xlSheet.Range("E" & row).Value = txtSampleCite.Text
xlSheet.Range("H" & row).Value = txtReporterType.Text
xlsWB1.SaveAs ("D:\xxxxx\POC\US Reporters_1234.xls")
xlsApp.Quit
Set xlsApp = Nothing
AppendDictionary
End
what this code does is, it detects the US Reporters_1234.xls and asks me if I want to overwrite it. If i say 'yes', i lose the values i entered the first time around and only the second iteration is retained. how can i keep appending this file?
i would want to know if it is possible to just back up the original (US Reporters.xls) and then create a copy of that (say, US Reporters_1234.xls) the first time around. Then, the code should look for this file, US Reporters_1234.xls and if it finds it, append it with the new values. else, just back up the original and create a new copy and work with it.
if this is possible, how do i do it? help is greatly appreciated.
thankkksss in advance...
i have a vb form (within word). this form has four text fields and a button. user enters some info in these text fields and on button click, these values are added into four columns (C, D, E and H) at the very end of the spreadsheet - after the last populated line. the next time i run this, it should add the info into the four columns in the next line -after the previous additions.
this is the code i have:
Private Sub cmdAppend_Click()
Dim xlsWB1 As Object
Dim xlsWS1 As Object
Dim strFileName As String
Dim oRange As Range
'strFileName = "D:\xxxxxxx\POC\US Reporters_123.xls"
'Opening the file to parse now
Set xlsApp = CreateObject("Excel.Application")
xlsApp.Visible = True 'Makes Excel File visible.
Set xlsWB1 = xlsApp.Workbooks.Open(strFileName)
Set xlSheet = xlsWB1.Worksheets(1)
xlSheet.Activate
xlSheet.Range("A1").Select
Dim col As Integer
Dim row As Integer
Dim maxrow As Integer
Dim maxcol As Integer
Dim str As String
Dim rListPaste As Range
str = ""
maxrow = xlSheet.Range("A1").CurrentRegion.Rows.Count
maxcol = 9
'Reading the Excel file and putting everything in Memory for faster manipulation
row = maxrow + 1
xlSheet.Range("C" & row).Value = txtReporterName.Text
xlSheet.Range("D" & row).Value = txtPrimaryAbbrev.Text
xlSheet.Range("E" & row).Value = txtSampleCite.Text
xlSheet.Range("H" & row).Value = txtReporterType.Text
xlsWB1.SaveAs ("D:\xxxxx\POC\US Reporters_1234.xls")
xlsApp.Quit
Set xlsApp = Nothing
AppendDictionary
End
what this code does is, it detects the US Reporters_1234.xls and asks me if I want to overwrite it. If i say 'yes', i lose the values i entered the first time around and only the second iteration is retained. how can i keep appending this file?
i would want to know if it is possible to just back up the original (US Reporters.xls) and then create a copy of that (say, US Reporters_1234.xls) the first time around. Then, the code should look for this file, US Reporters_1234.xls and if it finds it, append it with the new values. else, just back up the original and create a new copy and work with it.
if this is possible, how do i do it? help is greatly appreciated.
thankkksss in advance...