At the moment I directly open the *pri file in our Portfolio program called Advent Axys, and manually
update the ticker prices using a downloaded Excel format file from the BigCharts website which is saved as csv extension.
What I would like to do is develop a stand alone program in Visual Basic ( not in Excel VBA) to update the price file in our
Portfolio system automatically using the downloaded Excel format file csv extention file from the BigCharts.
But before that, I need to export the *.pri file from our Portfolio system in to Excel which still saves as *.pri extention.
Then once it updates, I import the updated *.pri file back in our Portfolio program.
I understand that the Excel VBA code can be incorporated in Visual Basic code provided there is an object declaration for
Excel file (In this case eventhough the both files are in Excel format, they don't have xls extention). Would anyone have any
ideas about how to use external files and Excel VBA code in Visual Basic?
Below is the code that I currently have
in Visual Basic. What I'm trying to accomplish is using the ticker (eg. msft) as a keyword search to look up in the price
file. If found , the price of that ticker from the test.csv file will be copied in to the price file which is test.pri. I
haven't ran it yet. Any thoughts or advice on this will be much appreciated.
Thank u so much
P.S Not able to upload the 2 files since their extention is different
Sub UpdatePrice(BigChartPath As String, BigChartName As String, AxysPricePath As String, AxysPriceName As String)
'Below are Excel VBA codes
'Uses the test.csv to look up tickers in test.pri and update the price in it
Dim PriceFile As Workbook, BigChartFile As Workbook
Dim PriceFileSheet As Worksheet, BigChartSheet As Worksheet
Dim MaxRows As Long
Dim PriceFileRow As Long
Dim BigChartRow As Long
Dim BigChartFound As Boolean
Call CheckBookOpen(BigChartPath & BigChartName)
Call CheckBookOpen(AxysPricePath & AxysPriceName)
Set BigChartFile = Workbooks(BigChartName) 'Big Chart website imported CSV file saved as test.csv
Set PriceFile = Workbooks(AxysPriceName) ' pri file imported from Advent Axys saved as test.pri
Set BigChartSheet = BigChartFile.Sheets(Sheet)
Set PriceFileSheet = PriceFile.Sheets(Sheet)
MaxRows = w1.Range("a65536").End(xlUp).Row
For BigChartRow = 2 To MaxRows
'On Error GoTo NotFound
BigChartFound = True
PriceFileRow = PriceFileSheet.Range("b:b").Find(BigChartSheet.Cells(BigChartRow, 2).Value).Row
If BigChartFound = True Then
PriceFileSheet.Cells(PriceFileRow, 3).Value = BigChartSheet.Cells(BigChartRow, 4).Value
PriceFileSheet.Cells(PriceFileRow, 3).Value = "not found"
'PriceFileRow = PriceFileRow + 1
'w3.Cells(w3Row, 1) = w1.Cells(w1Row, 1)
'w3.Cells(w3Row, 2) = w1.Cells(w1Row, 2)
'w3.Cells(w3Row, 3) = "not found"
'w3Row = w3Row + 1
'w2Found = False
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines