Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for using the full path to get a value
I'm almost there, but I just can't get it quit right. I am try to get a
value from a cell using the full path to the file. This ain't working, but it's close. What am I doing wrong? Sub Test() pName = "U:\ExcelFiles\ADI-JournalEntries\Current_Month\Advance-O&M_Cur.xls" MsgBox pName & Sheets("Journal1").Range("R22") End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for using the full path to get a value
Excel VBA doesn't support linking to closed workbooks like excel itself
does. John Walkenbach has documented a method that uses and Excel 4 macro to in VBA to do this. http://j-walk.com/ss/excel/tips/tip82.htm you can possibly use ADO, but that would seem more appropriate for a data table. http://www.erlandsendata.no/english/vba/adodao/ Andy Wiggins recently suggest sql.request This might be a help for getting data to and from Excel and Access: It includes examples of using variables in SQL queries. http://www.bygsoftware.com/examples/sql.html Or you can get there from the "Excel with Access Databases" section on page: http://www.bygsoftware.com/examples/examples.htm It demonstrates how to use SQL in Excel's VBA to: * create a database, * create a table and add data to it, * select data from a table, * delete a table, * delete a database. You can also download the demonstration file called "excelsql.zip". The code is open and commented. -- Regards Andy Wiggins www.BygSoftware.com =========================== -- Regards, Tom Ogilvy "Tommy Flynn" wrote in message ... I'm almost there, but I just can't get it quit right. I am try to get a value from a cell using the full path to the file. This ain't working, but it's close. What am I doing wrong? Sub Test() pName = "U:\ExcelFiles\ADI-JournalEntries\Current_Month\Advance-O&M_Cur.xls" MsgBox pName & Sheets("Journal1").Range("R22") End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for using the full path to get a value
Thanks. I'll take a look.
Tommy Flynn "Tom Ogilvy" wrote in message ... Excel VBA doesn't support linking to closed workbooks like excel itself does. John Walkenbach has documented a method that uses and Excel 4 macro to in VBA to do this. http://j-walk.com/ss/excel/tips/tip82.htm you can possibly use ADO, but that would seem more appropriate for a data table. http://www.erlandsendata.no/english/vba/adodao/ Andy Wiggins recently suggest sql.request This might be a help for getting data to and from Excel and Access: It includes examples of using variables in SQL queries. http://www.bygsoftware.com/examples/sql.html Or you can get there from the "Excel with Access Databases" section on page: http://www.bygsoftware.com/examples/examples.htm It demonstrates how to use SQL in Excel's VBA to: * create a database, * create a table and add data to it, * select data from a table, * delete a table, * delete a database. You can also download the demonstration file called "excelsql.zip". The code is open and commented. -- Regards Andy Wiggins www.BygSoftware.com =========================== -- Regards, Tom Ogilvy "Tommy Flynn" wrote in message ... I'm almost there, but I just can't get it quit right. I am try to get a value from a cell using the full path to the file. This ain't working, but it's close. What am I doing wrong? Sub Test() pName = "U:\ExcelFiles\ADI-JournalEntries\Current_Month\Advance-O&M_Cur.xls" MsgBox pName & Sheets("Journal1").Range("R22") End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for using the full path to get a value
Unless you wanted to open the file and get the value.
-- Regards, Tom Ogilvy "Tommy Flynn" wrote in message ... Thanks. I'll take a look. Tommy Flynn "Tom Ogilvy" wrote in message ... Excel VBA doesn't support linking to closed workbooks like excel itself does. John Walkenbach has documented a method that uses and Excel 4 macro to in VBA to do this. http://j-walk.com/ss/excel/tips/tip82.htm you can possibly use ADO, but that would seem more appropriate for a data table. http://www.erlandsendata.no/english/vba/adodao/ Andy Wiggins recently suggest sql.request This might be a help for getting data to and from Excel and Access: It includes examples of using variables in SQL queries. http://www.bygsoftware.com/examples/sql.html Or you can get there from the "Excel with Access Databases" section on page: http://www.bygsoftware.com/examples/examples.htm It demonstrates how to use SQL in Excel's VBA to: * create a database, * create a table and add data to it, * select data from a table, * delete a table, * delete a database. You can also download the demonstration file called "excelsql.zip". The code is open and commented. -- Regards Andy Wiggins www.BygSoftware.com =========================== -- Regards, Tom Ogilvy "Tommy Flynn" wrote in message ... I'm almost there, but I just can't get it quit right. I am try to get a value from a cell using the full path to the file. This ain't working, but it's close. What am I doing wrong? Sub Test() pName = "U:\ExcelFiles\ADI-JournalEntries\Current_Month\Advance-O&M_Cur.xls" MsgBox pName & Sheets("Journal1").Range("R22") End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for using the full path to get a value
I did a work-a-round. It's not rocket science, but it works.
Sub GetValue() Dim myRng As Range Set myRng = Sheets(2).Range("A1") If myRng.HasFormula Then myVar = myRng.Formula Else myVar = myRng.Value End If myRng.Formula = "='U:\ExcelFiles\ADI-JournalEntries\Current_Month\[Advance-O&M_Cur.xls]Journ al1'!$R$22" MsgBox Format(myRng, "###,###.#0") myRng = myVar End Sub Thanks for your help. Tommy "Tom Ogilvy" wrote in message ... Unless you wanted to open the file and get the value. -- Regards, Tom Ogilvy "Tommy Flynn" wrote in message ... Thanks. I'll take a look. Tommy Flynn "Tom Ogilvy" wrote in message ... Excel VBA doesn't support linking to closed workbooks like excel itself does. John Walkenbach has documented a method that uses and Excel 4 macro to in VBA to do this. http://j-walk.com/ss/excel/tips/tip82.htm you can possibly use ADO, but that would seem more appropriate for a data table. http://www.erlandsendata.no/english/vba/adodao/ Andy Wiggins recently suggest sql.request This might be a help for getting data to and from Excel and Access: It includes examples of using variables in SQL queries. http://www.bygsoftware.com/examples/sql.html Or you can get there from the "Excel with Access Databases" section on page: http://www.bygsoftware.com/examples/examples.htm It demonstrates how to use SQL in Excel's VBA to: * create a database, * create a table and add data to it, * select data from a table, * delete a table, * delete a database. You can also download the demonstration file called "excelsql.zip". The code is open and commented. -- Regards Andy Wiggins www.BygSoftware.com =========================== -- Regards, Tom Ogilvy "Tommy Flynn" wrote in message ... I'm almost there, but I just can't get it quit right. I am try to get a value from a cell using the full path to the file. This ain't working, but it's close. What am I doing wrong? Sub Test() pName = "U:\ExcelFiles\ADI-JournalEntries\Current_Month\Advance-O&M_Cur.xls" MsgBox pName & Sheets("Journal1").Range("R22") End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Full path-name in title bar ? | Excel Discussion (Misc queries) | |||
Insert Full Path | Excel Worksheet Functions | |||
Full path possible to be seen? | New Users to Excel | |||
Full path in title bar | Excel Discussion (Misc queries) | |||
full UNC path in footer | Excel Discussion (Misc queries) |