Vba Code to write in a table

[beachu]

Hi! I´m really new in this programming world and I feel completely lost.

I have an excel table and a need programming in VB a series of formulas and add the result column in the table in access or in csv file.

What I did until now, was open the file and try to read the file, but it has 7 columns...and i don´t know if want did with the arrays is possible (it has error 62)

And after this problem, I don´t how to create a new column in access as a result of a formula using the values of the column in access.

I hope someone can help me

I´m very grateful


Bea

Public Function HargreavesFun()
Dim dblArray(1000, 1000) As Double
Dim intNr As Double
Dim dblData As Double
Dim strPath As String
'Open a file for reading
    strPath = "G:\geog5540_assignment1\Project6Data_mod2.xlsx"
        intFileHandle = FreeFile
    
    Open "G:\geog5540_assignment1\Project6Data_mod2.xlsx" For Input Access Read Shared As #1
    
    
' Loop to read in the file
       
    intNr = 0
    Do Until EOF(1)
        intNr = intNr + 1
        Input #1, dblArray(intNr, 0), dblArray(intNr, 1), dblArray(intNr, 2), dblArray(intNr, 3), dblArray(intNr, 4), dblArray(intNr, 5), dblArray(intNr, 6), dblArray(intNr, 7)
    Loop
'Statement to Close the file
    Close (1)

'Hargreaves and Samani (1985)formula to calculate Evapotranspiration
    Dim i As Integer
    Dim dblHargreaves_evapotranspiration As Double
    Dim dblRa As Double
    Dim dblTmax, dblTmin As Double
    
    
    'Loop to calculate the ET based on Hargreaves formula
        For i = 0 To 366
            'dblHargreaves_evapotranspiration(i) = 0.0023 * dblRa * ((dblTmaxi + dblTMini) / 2) + 17.8 * Sqr(dblTmaxi - dblTMini)
        'Next i

End Function

 

[twoplustwo]

Hi,

You don't need to store calculated fields in the table.

Import all of your data and write a query with

Evap: 0.0023 * dblRa * ((dblTmaxi + dblTMini) / 2) + 17.8 * Sqr(dblTmaxi - dblTMini)

in one of the columns. If those values are static and the rest imported into the database there is no need to add columns/store the calculated fields etc. Calculating them at run time is much more efficient.

 

[DJkarl]

Open "G:\geog5540_assignment1\Project6Data_mod2.xlsx" For Input Access Read Shared As #1

You are trying to open an Excel file as a text file, this will most likely not work. If you open an Excel file in Notepad you can see all the additional data that gets stored with it. Try using TransferSpreadsheet to import or link to the file then use it as a table.

If you really don't want to import or link the spreadsheet then you will need to use Excel automation to open the workbook and reference it that way.

 

[bulrush]

DJKarl,
Isn't an XSLX file the new Microsoft XML file storage method for Office 2007? That would mean it is indeed a text file in XML format.

 

[DJkarl]

You are correct about the xlsx format being based on XML.

It is plain text...but there are much better ways to read in an XML file then trying to use the open method. Open is great for fixed width or binary files.