Reading Excel sheet from a Folder

[hdagar]

Hi,

I can read an Excel file from the following code,however I am not sure where i am going to provide the path of sheet located in a folder.

Can somebody help?I am new to VB.

Public OD As Double, OD1 As Double, BWG As Double, BWG1 As Double, Pr As Double, K1 As Double
Public irow As Integer, icol As Integer
Sub Corr_Factor()
Dim objXL As Excel.Application
Dim objSHEET As Excel.Worksheet
Dim objWBOOK As Excel.Workbook
Set objXL = ActiveWorkbook.Excel4IntlMacroSheets.Application
'--Point to Data Sheet--
Set objSHEET = objXL.Sheets("Corr Factor K1")
objXL.Sheets("Corr Factor K1").Select

OD = objXL.ActiveSheet.Cells(55, 2).Value
BWG = objXL.ActiveSheet.Cells(56, 2).Value
Pr = objXL.ActiveSheet.Cells(57, 2).Value

Thanks

[Jewe]

Hi,

Dim mcApp As Excel.Application
Set mcApp = New Excel.Application
mcApp.Workbooks.Open "your file"

for opening a file.

if this is not what you are looking for please redefine your question.

with kind regards,

Jeffrey

[hdagar]

Thanks Buddy.

If I understand it correctly

mcApp.Workbooks.Open "your file"

In place of your file in double quotes...I need to specify the path of my Excel sheet..like "C:\Hdagar\sheet1.xls"....

Please correct me if i am wrong.

Thanks

[Jewe]

That is correct.

if you got any other excel problems just post them here..

and I'll see if I know the answer....

[hdagar]

Thank you very much.

[hdagar]

Hi Jewe,

I am facing this run time error '9' on the piece of code below:

Set objSHEET = objXL.Sheets("C:\Model.xls").

Code above this code is:

Private Sub CommandButton7_Click()

Dim OD As Double, OD1 As Double, BWG As Double, BWG1 As Double, Pr As Double, K1 As Double
Dim irow As Integer, icol As Integer
Dim objXL As Excel.Application
Dim objSHEET As Excel.Worksheet
Dim objWBOOK As Excel.Workbook
Set objXL = ActiveWorkbook.Excel4IntlMacroSheets.Application.

My requirement is to read the Excel file "Model" lying at C:\...drive.In Excel file I need to read the Worksheet named as "CYCLE REQUEST".

From Cycle reuqest sheet,I need to to get the values of a particular cell for which i am using following code:

OD = objXL.ActiveSheet.Cells(8, 2).Value.

Could you please share your thoughts on this.

[Jewe]

Hi,

error 9 = Subscript out of range

looks like you did not actually load the excel file.

    Dim cExcel As Excel.Application
    Dim cSheet As Excel.Worksheet
    
    Set cExcel = New Excel.Application
    'see what we are doing
    cExcel.Visible = True
    'open the file
    cExcel.Workbooks.Open "C:\Temp\a.xls"
    
    'second sheet is called jewe in my case
    Set cSheet = cExcel.Sheets("jewe")
    'so that we can see some action
    cSheet.Activate
    'add some text
    cSheet.Cells(1, 1) = "Jewe testing"

[hdagar]

Thanks a lot.

Actually I got the problem after posting this and it is working fine.

Thanks again.