Hi,
I wrote a quick program which I want to use to take certain cells of one Excel sheet and copy them into another. Both spreadsheets have been created and have data in them. My code runs and doesn't come back with any errors. When I open the Excel spreadsheet I want to write to after running the program nothing is updated. What am I doing wrong? Thanks in advance. Here is my code:
Sub Main()
Dim ExcelObject1 As Microsoft.Office.Interop.Excel.Workbook 'Excel Workbook Object (for import spreadsheet)
Dim ExcelWorksheet1 As Microsoft.Office.Interop.Excel.Worksheet 'Excel Worksheet Object (for import spreadsheet)
Dim ExcelObject2 As Microsoft.Office.Interop.Excel.Workbook 'Excel Workbook Object (for export spreadsheet)
Dim ExcelWorksheet2 As Microsoft.Office.Interop.Excel.Worksheet 'Excel Worksheet Object (for export spreadsheet)
Dim R1 As Integer 'Row counter for import spreadsheet
Dim C1 As Integer 'Column counter for import spreadsheet
Dim R2 As Integer 'Row counter for export spreadsheet
Dim C2 As Integer 'Column counter for export spreadsheet
Dim WS As Integer 'Worksheet counter
Dim OldR2 As Integer
Const FromSpreadsheet As String = "C:\Documents and Settings\socha\My Documents\University Planning\Regular Reports\Applicants, Admits, & Enrolled Comparisons\Hist_2005 Modified.xls"
Const ToSpreadsheet As String = "C:\Documents and Settings\socha\My Documents\University Planning\Regular Reports\Applicants, Admits, & Enrolled Comparisons\FTF Comparison Data.xls"
ExcelObject1 = GetObject(FromSpreadsheet)
ExcelObject2 = GetObject(ToSpreadsheet)
ExcelWorksheet2 = ExcelObject2.Worksheets(1)
R2 = 2
C2 = 5
OldR2 = 2
For WS = 1 To 34
ExcelWorksheet1 = ExcelObject1.Worksheets(WS)
For C1 = 2 To 35
For R1 = 6 To 136
If C1 <> 6 And C1 <> 11 And C1 <> 16 And C1 <> 21 And C1 <> 26 And C1 <> 31 And R1 <> 7 And R1 <> 8 And R1 <> 11 And R1 <> 12 And R1 <> 16 And R1 <> 17 And R1 <> 28 And R1 <> 29 And R1 <> 41 And R1 <> 42 And R1 <> 49 And R1 <> 51 And R1 <> 52 And R1 <> 60 And R1 <> 61 And R1 <> 62 And R1 <> 63 And R1 <> 70 And R1 <> 71 And R1 <> 72 And R1 <> 73 And R1 <> 74 And R1 <> 76 And R1 <> 77 And R1 <> 80 And R1 <> 81 And R1 <> 85 And R1 <> 86 And R1 <> 97 And R1 <> 98 And R1 <> 110 And R1 <> 111 And R1 <> 118 And R1 <> 119 And R1 <> 127 And R1 <> 128 And R1 <> 129 And R1 <> 130 Then
ExcelWorksheet2.Cells(R2, C2) = ExcelWorksheet1.Cells(R1, C1)
R2 += 1
End If
Next R1
R2 = OldR2
If C2 = 11 Then
C2 = 5
Else
C2 += 1
End If
Next C1
If C1 = 6 Or C1 = 11 Or C1 = 16 Or C1 = 21 Or C1 = 26 Or C1 = 31 Then
OldR2 += 369
R2 = OldR2
End If
Next WS
ExcelObject1.Close(True, FromSpreadsheet)
ExcelObject2.Close(True, ToSpreadsheet)
End Sub