Hello, so far this program works except for one major problem. I will explain the point as quickly as possible. Multiple users will submit this workbook each day (about 15). The person who recieves them will hit a "upload schwab button" where the data input by the users is pasted into another workbook (called OutputBook). Notice that the data that needs to be copied is determined in teh first few lines, (this works fine). Next, thanks to you guys, the sheet determines if OutputBook has already been created for the day. If it has not, Outputbook is created and the data is pasted into the first few lines. Everything up to this point works.
If OutputBook already exists, the file is opened, and I want the data to paste below the previsouly pasted data. (the program is successful in deciding where to paste it) The only problem here is that the program has now forgot the pasted range. I've isolated the problem to when the OutputBook is opened. (See the --><---) Before this line is executed, the pasted data is still int eh computers memory. Afterward, it is gone. I've tried recopying the data from the original worksheet, but that seems to generate its own set of errors. Anyone know why?
Public Function UploadSchwab()
Dim PreviousOutput As Range
Dim Location As Integer
Dim OutputBook As String
'This section copies each trade recorded on the Schwab output sheet. This is done by copying a range of cells _
A2 : to cells(OutputTotal, Column 21). Outputtotal is a variable created on the Advisor sheet and reflects the _
total number of trades input, plus 1 row to account for the master account header. The Advisor view sheet is _
reactivated so the user is unaware of what has happened.
Worksheets("Schwab Output").Activate
Worksheets("Schwab Output").Range(Cells(2, 1), Cells(Worksheets("Advisor View").Range("Outputtotal").Value + 1, 21)).Select
Selection.Copy
Worksheets("Advisor View").Activate
' This section first determines the name of today's output workbook. The code then asks, does this file exist(Outputbook = "")? _
If not, then it adds a workbook, pastes the output data in the beginning rows, adds some extraneous data in cell a1 _
and b1, and saves the new workbook as the name of the Output Workbook
OutputBook = "C:\Documents and Settings\All Users\Desktop\" & Format(Date, "yymmdd") & " Schwab Trades.csv"
If Dir(OutputBook) = "" Then
Workbooks.Add
Set wbOut = ActiveWorkbook
Cells(2, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("a1").Value = 8007706
Range("b1").Value = 2
wbOut.SaveAs OutputBook
'This section exists in case the OutputBook has already been created, and instead the user is adding export data in addition _
to the data already present in the book. It determines what the previousOutput was by counting how many lines are blank _
in the first 100 rows. It minuses that number by 101, so if fifty rows of previous data existed, the cell where new Data should be pasted _
is row 51 (101-50 blank rows, column 1 "Cells(Location, 1)" The data is then pasted and the workbook is simply saved.
Else
-------------> Workbooks.Open (OutputBook)<-----------------------------
Set PreviousOutput = Range("a2:a100")
Location = 101 - Application.WorksheetFunction.CountBlank(PreviousOutput)
Cells(Location, 1).Select
Selection.PasteSepcial Paste:=x1PasteValues
ActiveWorkbook.Save
End If
End Function
If OutputBook already exists, the file is opened, and I want the data to paste below the previsouly pasted data. (the program is successful in deciding where to paste it) The only problem here is that the program has now forgot the pasted range. I've isolated the problem to when the OutputBook is opened. (See the --><---) Before this line is executed, the pasted data is still int eh computers memory. Afterward, it is gone. I've tried recopying the data from the original worksheet, but that seems to generate its own set of errors. Anyone know why?
Public Function UploadSchwab()
Dim PreviousOutput As Range
Dim Location As Integer
Dim OutputBook As String
'This section copies each trade recorded on the Schwab output sheet. This is done by copying a range of cells _
A2 : to cells(OutputTotal, Column 21). Outputtotal is a variable created on the Advisor sheet and reflects the _
total number of trades input, plus 1 row to account for the master account header. The Advisor view sheet is _
reactivated so the user is unaware of what has happened.
Worksheets("Schwab Output").Activate
Worksheets("Schwab Output").Range(Cells(2, 1), Cells(Worksheets("Advisor View").Range("Outputtotal").Value + 1, 21)).Select
Selection.Copy
Worksheets("Advisor View").Activate
' This section first determines the name of today's output workbook. The code then asks, does this file exist(Outputbook = "")? _
If not, then it adds a workbook, pastes the output data in the beginning rows, adds some extraneous data in cell a1 _
and b1, and saves the new workbook as the name of the Output Workbook
OutputBook = "C:\Documents and Settings\All Users\Desktop\" & Format(Date, "yymmdd") & " Schwab Trades.csv"
If Dir(OutputBook) = "" Then
Workbooks.Add
Set wbOut = ActiveWorkbook
Cells(2, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("a1").Value = 8007706
Range("b1").Value = 2
wbOut.SaveAs OutputBook
'This section exists in case the OutputBook has already been created, and instead the user is adding export data in addition _
to the data already present in the book. It determines what the previousOutput was by counting how many lines are blank _
in the first 100 rows. It minuses that number by 101, so if fifty rows of previous data existed, the cell where new Data should be pasted _
is row 51 (101-50 blank rows, column 1 "Cells(Location, 1)" The data is then pasted and the workbook is simply saved.
Else
-------------> Workbooks.Open (OutputBook)<-----------------------------
Set PreviousOutput = Range("a2:a100")
Location = 101 - Application.WorksheetFunction.CountBlank(PreviousOutput)
Cells(Location, 1).Select
Selection.PasteSepcial Paste:=x1PasteValues
ActiveWorkbook.Save
End If
End Function