CODEWITHSUNDEEP
vb6
Zaheer4444
Zaheer4444

Reputation: 19

Want Excel Coding in VB6

I want to get value of Column A of Sheet1 of excel file into Variable string and after changing its value, i want to paste it in Column A of another Sheet2 of same excel file.

Upvotes: 0

Views: 980

Answers (1)

Ahmad
Ahmad

Reputation: 12707

Here is what you need to do:

First make sure that EXCEL is referenced in your VB6 References, by accessing: Project>References>Microsoft Excel XX Object Library

Where XX is the version of Office installed on the PC.

Dim objEX As New Excel.Application

objEX.Visible = True 
' Optional if you want to see what is going on in EXCEL 
' while your code is being executed.


    objEX.Workbooks.Open "C:\My Files\Filename.xls"    
        'Make sure you put the right path of the excel workbook you want to open

With objEX
    'If you know the name of the sheet you want to read from 
    ' then use this code
    .Sheets("FIRST_Sheet_Name_Here").Activate

    'Otherwise, you may only know that the sheet is 
    ' physically the first sheet in that workbook, then use this code:
    .Sheets(1).Activate

    Dim myValue As Double

    myValue = .Range("A1").Value  
   ' Change A1 to whatever Cell you want to read from the sheet

    'Now we will do something with the value that we read and 
    ' then will save it back to EXCEL in sheet 2 and cell B2 for example
    myValue = Val(myValue) + 1000

    .Sheets("SECOND_Sheet_Name_Here").Activate   
    'if you know that name of the second sheet


    .Sheets(2).Activate                          
    'if you don't know the name, but know the location

    .Range("B2").Value = myValue                 
    ' This will write the variable to the location B2 in sheet 2



    .ActiveWorkbook.Save                         
    'Saves the changes you have done


    .ActiveWorkbook.Close                        
    'Close the workbooks but keeps Excel application open


    .Quit                                        
    'Quits excel instance and releases the process from task manager

End With

    Set objEX = Nothing            
   'Garbage Collection and making sure memory is released to other processes.

Upvotes: 1

Related Questions