HELPNewToVB

Can someone help me out with the syntax of opening an existing excel file using a button click event. What to dim and all I know it should be easy, but I'm a little confused. I've seen a few different ways...Also how many sheets are automatically in a workbook that you create 3 If so I need 4! I'm using Excel 2007 and Visual Studio 2005. I've already referenced the Excel Object library...

Thank you!



Re: Visual Basic Language Opening an existing excel file

Karthikeya Pavan Kumar .B

check this link




Re: Visual Basic Language Opening an existing excel file

HELPNewToVB

Can someone tell me whats wrong with this code I'm trying to open an existing excel workbook using visual studio 2005 and Excel 2007. I've already referenced the excel object library.

Dim oApp As Excel.Application

oApp = CreateObject("Excel.Application")

Dim Wkbk As Excel.Workbook

Wkbk = oApp.Workbooks.Open("C:\Documents and Settings\test.xls") <------I'm getting an error here that says

oApp.Visible = True member not found, but my file is there

Anything will be helpful! Thank you!





Re: Visual Basic Language Opening an existing excel file

HELPNewToVB

Can someone tell me whats wrong with this code I'm trying to open an existing excel workbook using visual studio 2005 and Excel 2007. I've already referenced the excel object library.

Dim oApp As Excel.Application

oApp = CreateObject("Excel.Application")

Dim Wkbk As Excel.Workbook

Wkbk = oApp.Workbooks.Open("C:\Documents and Settings\test.xls") <------I'm getting an error here that says

oApp.Visible = True member not found, but my file is there

Anything will be helpful! Thank you!





Re: Visual Basic Language Opening an existing excel file

HELPNewToVB

Can someone tell me whats wrong with this code I'm trying to open an existing excel workbook using visual studio 2005 and Excel 2007. I've already referenced the excel object library.

Dim oApp As Excel.Application

oApp = CreateObject("Excel.Application")

Dim Wkbk As Excel.Workbook

Wkbk = oApp.Workbooks.Open("C:\Documents and Settings\test.xls") <------I'm getting an error here that says

oApp.Visible = True member not found, but my file is there

Anything will be helpful! Thank you!





Re: Visual Basic Language Opening an existing excel file

Derek Smyth

Hi mate,

This code works fine the only difference is the CreateObject method, you don't need it but having it shouldn't be a problem....

Code Snippet

Dim xl As New Excel.Application

Dim wrkBk As Excel.Workbook

wrkBk = xl.Workbooks.Open("C:\Documents and Settings\test.xls")

xl.Visible = True

Try that code as see if it works, also, sorry, but just make sure your file is there and that you have permission to read it. If you created it then you'll have permission.






Re: Visual Basic Language Opening an existing excel file

HELPNewToVB

Thank you for your help! I relized that I had to do it like this, I guess its new in Visual Studio 2005

Dim oApp = New Excel._ExcelApplication

oApp = CreateObject("Excel.Application")

Dim Wkbk As Excel.Workbook

Wkbk = oApp.Workbooks.Open("C:\Documents and Settings\test.xls")

oApp.Visible = True

Ok, now I have another question Smile I'm trying to select certain columns from this sheet and paste it into a new workbook:

Dim oApp = New Excel._ExcelApplication

oApp = CreateObject("Excel.Application")

Dim Wkbk As Excel.Workbook

Wkbk = oApp.Workbooks.Open("C:\Documents and Settings\test.xls")

oApp.Visible = True

Dim Sht As Excel.Worksheet = LCV.Sheets(1)

Dim Rg1 As Excel.Range = Sht.Cells("B:F").select

Dim Rg2 As Excel.Range = Sht.Cells("H").select

Dim Rg3 As Excel.Range = Sht.Cells("G").select

Dim Rg4 As Excel.Range = Sht.Cells("J").select

Dim Rg5 As Excel.Range = Sht.Cells("S").select

Dim Rg6 As Excel.Range = Sht.Cells("R").select

Dim Rg7 As Excel.Range = Sht.Cells("I").select

Dim Rg8 As Excel.Range = Sht.Cells("T").select

Dim Rg9 As Excel.Range = Sht.Cells("BV:BX").select

Dim Rg10 As Excel.Range = Sht.Cells("BP:BR").select

Dim Rg11 As Excel.Range = Sht.Cells("CA:CB").select

Dim Rg12 As Excel.Range = Sht.Cells("BS").select

Dim Rg13 As Excel.Range = Sht.Cells("U:V").select

Dim Rg14 As Excel.Range = Sht.Cells("A").select

Rg1.Copy()

Is there an easier way to do this Or am I doing it completely the long way

...and should I just pasteSpecial each selection where i need it to go in the new workbook once I've created it with

Thanks in advance! Smile





Re: Visual Basic Language Opening an existing excel file

Derek Smyth

Hi man,

Glad you got the excel loading, surprised you had to use the underscore but hey so what it works.

Wouldn't say your doing the copy the wrong way, there is no right or wrong way, but I think your not doing it the best way as in your roach your going to have a lot of code thats doing the same thing. You have a method that works, select, copy and then paste, your just going to have a lot of code, the more code you have the more maintenance and the more difficult it is to update. Try this approach instead...

Use Range("B:F") instead of Cells("B:F"), Cells uses the row and column index. Cells(1, 1) = A1.

Dim cellsToCopy() as String = { "B:F", "H", "G", "J" } 'add the other cells here too

For Each cellToCopy as String in cellsToCopy

Dim Rg1 As Excel.Range = Sht.Range(cellToCopy).Select

Rg1,Copy()

'do your paste in here

Next

I haven't ran the above code but hopefully it will help.






Re: Visual Basic Language Opening an existing excel file

HELPNewToVB

Oh ok awesome. Thank you! I knew there was a more efficient way to do it, but just wasn't sure how to get it done. I'll try it, but it looks good thanks!



Re: Visual Basic Language Opening an existing excel file

HELPNewToVB

Sorry I know this is one of the easier questions, but on the cellToCopy in red below, it's saying " 'Variable' cellToCopy hides a variable in an enclosing block"

Dim cellsToCopy() as String = { "B:F", "H", "G", "J" } 'add the other cells here too

For Each cellToCopy as String in cellsToCopy

Dim Rg1 As Excel.Range = Sht.Range(cellToCopy).Select

Rg1,Copy()

'do your paste in here

Next





Re: Visual Basic Language Opening an existing excel file

Derek Smyth

You must have defined a variable called cellToCopy already and the two names are clashing, just change the loop to, or to something else, just two variables with the same names...

Dim cellsToCopy() As String = {"B:F", "H", "G", "J"} 'add the other cells here too

For Each cell As String In cellsToCopy

'do your paste in here

Next






Re: Visual Basic Language Opening an existing excel file

HELPNewToVB

oh ok! thanks again, you've been a really big help!



Re: Visual Basic Language Opening an existing excel file

Naga Satish Rupenaguntla

try using the escape characters for \

try c:\\Documents and Settings\\test.xls instead of c:\Documents and Settings\text.xls






Re: Visual Basic Language Opening an existing excel file

Naga Satish Rupenaguntla

try using the escape characters for \

try c:\\Documents and Settings\\test.xls instead of c:\Documents and Settings\text.xls