Collecting Range Name values to VBA

I have a worksheet "Setup" where users type in a date in a cell named 
"ChtDte" and a path and database name in a cell named "FLName".  I am using 
DOA to connect to a database and return a record set.  The query used 
"qryCOCostwRates" uses a date paramater.  Because this sheet will be used by 
several users all pointing to the database in different locations, I need to 
know where they have the database.

I need to get the values in these two range names in the setup tab of the 
spreadsheet so I can connect to the data, and provide a value for the 
paramiter.  This should be easy but I can't seem to find the solution.  Any 
ideas?  Here's what I have tried:

Dim xlwsSetup As Excel.Worksheet
Dim xlrngFl As Excel.Range
Dim xlrngDte As Excel.Range
Dim xlWb As Excel.Workbook
Dim dbFln as String
Dim RptDte as Date
Set xlWb = ActiveWorkbook
Set xlwsSetup = xlWb.Worksheets("Sheet3")
Set xlwsSetup = ActiveSheet
Set xlrngFl = xlwsSetup.Range("FlName") 

'The above is cell C3 but it may change so I named the range "FLName". The 
user types "C:\Data\db\mydb.mdb" into the cell.

Set xlrngDte = xlwsSetup.Range("ChtDte")
'The above is cell C2 and it has a date in it like 2/28/09. If rows or 
columns are inserted, I don't want to lose the reference so it's named ChtDte.

dbFln = xlrngFl.Value
RptDte = xlrngDte.Value

Is the file path and name (FLName) and the report date (ChtDte) now in my 
variables?


-- 
Thanks in advance!
**John**
0
Utf
2/18/2010 10:39:01 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
1837 Views

Similar Articles

[PageSpeed] 12

I got it worked out... Thanks for all the help...
-- 
Thanks in advance!
**John**


"John" wrote:

> I have a worksheet "Setup" where users type in a date in a cell named 
> "ChtDte" and a path and database name in a cell named "FLName".  I am using 
> DOA to connect to a database and return a record set.  The query used 
> "qryCOCostwRates" uses a date paramater.  Because this sheet will be used by 
> several users all pointing to the database in different locations, I need to 
> know where they have the database.
> 
> I need to get the values in these two range names in the setup tab of the 
> spreadsheet so I can connect to the data, and provide a value for the 
> paramiter.  This should be easy but I can't seem to find the solution.  Any 
> ideas?  Here's what I have tried:
> 
> Dim xlwsSetup As Excel.Worksheet
> Dim xlrngFl As Excel.Range
> Dim xlrngDte As Excel.Range
> Dim xlWb As Excel.Workbook
> Dim dbFln as String
> Dim RptDte as Date
> Set xlWb = ActiveWorkbook
> Set xlwsSetup = xlWb.Worksheets("Sheet3")
> Set xlwsSetup = ActiveSheet
> Set xlrngFl = xlwsSetup.Range("FlName") 
> 
> 'The above is cell C3 but it may change so I named the range "FLName". The 
> user types "C:\Data\db\mydb.mdb" into the cell.
> 
> Set xlrngDte = xlwsSetup.Range("ChtDte")
> 'The above is cell C2 and it has a date in it like 2/28/09. If rows or 
> columns are inserted, I don't want to lose the reference so it's named ChtDte.
> 
> dbFln = xlrngFl.Value
> RptDte = xlrngDte.Value
> 
> Is the file path and name (FLName) and the report date (ChtDte) now in my 
> variables?
> 
> 
> -- 
> Thanks in advance!
> **John**
0
Utf
2/19/2010 10:43:01 PM
Reply:

Similar Artilces:

Excel Background collection (GIF and JPEG)
I use the Sheet<Background option in excel a lot. Anybody know of a place on microsoft.com or other websites where I can find a useful collection of GIFs and JPGs suitable for Excel Background? Thanks for help Madan I don't. But I do know that google has an image search. http://www.google.com/imghp Mohan wrote: > > I use the Sheet<Background option in excel a lot. Anybody know of a place > on microsoft.com or other websites where I can find a useful collection of > GIFs and JPGs suitable for Excel Background? > > Thanks for help > Madan -- Dave Pete...

Excel /vba / computer IP address
I need urgent help. How can I protect an excel file to a specific computer using VBA? (A excel file that can only be open or used in a specific computer) I was thinking in using the computer IP address, does somebody knows how can I get the computer IP, using visual basic in a excel worksheet? I would really appreciate you help Thanks (My email fcarvalho@portugalmail.com) -- jolipe ------------------------------------------------------------------------ jolipe's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24024 View this thread: http://www.excelforum.c...

Internal and External domain name behind firewall
Hi, I have a 2003 exchange server installed behind a firewall that forwards port 25 traffice to the exchange server. My question is that I have a different external domain vs my internal domain name. How would I configure this properly on the exchange server so that is can recieve mail via smtp. thx jason On Tue, 31 May 2005 12:39:30 -0600, "jason.sigurdur" <comp.techs@aspenview.org> wrote: >Hi, I have a 2003 exchange server installed behind a firewall that forwards >port 25 traffice to the exchange server. My question is that I have a >different external do...

Chart two variables with differing values
I'd like to chart data similar to: Year 2000 2001 2002 2003 2004 Thefts 75 120 95 101 80 Veh. Mfg. 100,200 125000 130000 145000 125000 I want to produce a chart with the year on the abcissa and with the Thefts scale on left ordinate and Veh. Mfg on the right ordinate and have the scales set accordingly. I'm running Excel 2002. Seems i can't select the Format/Selected Series data Series/Secondary Axis to set the scales correctly. I'd like to get som...

File name extension
Would anyone know how to reference a particular tab name at the end of a file extension. e.g. file name = workbook 1 worksheet name = tab 1 (in workbook 1) therefore C:\worbook 1\tab 1.xls or something of that nature. Sleepy, In VBA it would be Workbooks("workbook 1.xls").Worksheets("tab 1") In a formula, if workbook 1 is open, and you want to reference the value in cell A1: ='[workbook 1.xls]tab 1'!$A$1 if workbook 1 is closed: ='C:\Foldername\[workbook 1.xls]tab 1'!$A$1 HTH, Bernie MS Excel MVP "Sleepy" <johnnyyakoo@yahoo.ca> ...

Changing the Exchange Orginization Name
I'm trying to mount a mailbox store that was from another domain. After using esutil to clean the db, it still won't mount. The error I get points out that the Distinctive names are different. Event Type: Error Event Source: MSExchangeIS Event Category: General Event ID: 1088 Date: 11/19/2003 Time: 11:10:31 PM User: N/A Computer: MACHINE_A Description: The information store could not be loaded because the distinguished name (DN) /O=ABC CORPORATION/OU=FIRST ADMINISTRATIVE GROUP/CN=RECIPIENTS/CN= of message database "First Storage Group\Mailbox Store (MACHINE_A)" does not m...

Trying begin VBA template project, get error "macros disabled"?
I am beginning a project to code and update a Word template using VBA. When I try to run the form given to me I get error "The macros in this project are disabled?" Any suggestions? Thank you. Save the template in your templates folder, which is a trusted location. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "The Applicant" <The Applicant@discussions.microsoft.com> wrote in message news:D2E642C...

If Count is not in a certain range, delete Rows
I have a spreadsheet that is 59752 x 2, with the one column Contact ID A and the other column Contact Link ID. It is like an instant messaging system. It looks something like this: 1 2 1 3 1 15 2 1 2 15 I want to count by different values in column A, and any count that is not within one of several ranges, those rows get deleted. So if the range is 3-5 contacts, then I want to keep the set of 1s, but delete the set of 2s. My ranges are 4-5 contacts, 30-41 contacts, and 100+ contacts. I am also brand new to macros, but I have experience with some other coding ...

outlook reqeusting user name and password
with in the pass few weeks some of the client machines running Outlook 2002 have started asking for the users name, password and domain. even though the client is logged onto the network already, the client cannot log onto Outlook. repeated attemps with the correct user name and password always fail. the problem started with one machine, then another, then another. Now only two of our 6 client machines can log into Outlook. Any idea? uhm, have their passwords expired? Have you verified the clients are configured to using the appropriate security? "Bob" <bob@data-r...

Date Range on Report in Money 2003
I have a favorite report in Money 2003 which lists scheduled payments within a range of dates. My file was upgraded from Money 2000. I used to have the problem of the report omitting many of the scheduled transactions within the date range. That was fixed by running the Salvage program. Now the problem is the other way around: the report shows transactions BEYOND the date range, as well as those within. Is there a fix for that? I still have the Salvage program. :-) Thanks, Elliott ...

Named Printers #4
I want to use Named Printers for PA Purchase Order Entry. However, when I choose Project from the Task Series drop-down, nothing is showing up. What could be the problem? -Hari ------=_NextPart_0001_6AC20EE0 Content-Type: text/plain Content-Transfer-Encoding: 7bit In this list, "Project" doesn't mean Project Accounting, that is just the series. Any application/module can put themselves in this list in any location- however it would seem the perfect location for Project Accounting report. Looking at my list, this also is empty for me as well. Briefly looking a PA sourc...

How to pass value from one form to another form.
I have the following code in BookList.aspx: <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="LibSysDataSource" AllowPaging="True"> <Columns> <asp:BoundField DataField="AccessionNo" HeaderText="Accession No" /> <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title&qu...

Duplicate Payee Names for Credit Card Accounts
My wife and I both have credit cards through Capital One that we pay electronically via MSN Billpay. However, it is very difficult to ensure that I select the correct payee account when entering payments. See http://umpmfaq.info/faqdb.php?q=65. "Bill Kendrick" <wg_kendr@bellsouth.net> wrote in message news:OIEoIHeOEHA.3964@TK2MSFTNGP10.phx.gbl... > My wife and I both have credit cards through Capital One that we pay > electronically via MSN Billpay. However, it is very difficult to ensure that > I select the correct payee account when entering payments. > > ...

passing file name
hye..how to pass the file name that have been read in this function to another function?here's my openfile code.. void CPIDDlg::OnFileOpen() { this->UpdateData(); CFile f; char DataFile[] = { "NUM Files (*.num)|*.num||" }; CFileDialog FileDlg(TRUE,".num",NULL,0,DataFile); if( FileDlg.DoModal() == IDOK) { CString File = FileDlg.GetFileName(); if(f.Open(FileDlg.GetFileName(), CFile::modeRead) == FALSE ) { InitPidDisplay(File); return; } else f.Close(); if (!InitPidDisplay(File)) AfxMessageBox("Failed to load data"); els...

Display ranges from one worksheet to another
I am running excel 2003. I have been given the task of creating a workbook where on one main sheet I want to display named ranges from up to 4 other worksheets based on the user selection. The ranges are work weeks and we want to be able to display 8 weeks at a time for resource planning. Can someone point me in a direction where I can find a way to do this? Thank you. Check Ron's site under the Copy/Paste section. http://www.rondebruin.nl/tips.htm "Opal" <tmwelton@hotmail.com> wrote in message news:eb392038-e6ab-46b0-a3d6-eb0df5c7a0fd@h2g2000vbd...

CSocket.Connect() Port Value
All: What is the best way to determine the "Host Port" value in the CSocket.Connect() function? The examples use the value 700 or greater. Can there be a conflict if I just use the value 700? -- Regards; Bruce Kingsley The correct port value to use is the port number of the port you are trying to connect to. That is determined by your host. If you are writing the host, unless you are implementing a standard protocol, it should not be < 1024. It must not be hardwired unless you have a registered port number (which you must get from IANA, the Internet Assigned Number Autho...

display last value in a column
I would like to display the last value unequal to zero recorded in a column in a specific cell. For example, A1 is to display the last value unequal to zero recorded in column B. The number of entries and values in column B changes frequently; therefore the last value in column B is in a different row. Thanks. -- Mike Try this... Assuming there are no logical TRUE entries in the range. =LOOKUP(1E100,1/B2:B100,B2:B100) -- Biff Microsoft Excel MVP "Mike" <Mike@discussions.microsoft.com> wrote in message news:DFFCBDB0-3632-46AC-A3AB-073E6F0D475...

Field values used to filter a report
I have a report that is based upon a query that filters the data in a variety of ways. The net result is a listing of Zip Codes that I ultimately use as a part of the data for a report. The contents of this report is limited to the Zip Codes from the query. (Not a true filter) I need to be able to print this list of zip codes in a comma seperated list in the report header. I am sure this can be easily accomplished but for the life of me I cannot figure this out. Any help would be greatly appreciated. Thanks in advance. There are 2 parts to your question: a) How to provide an interface ...

Excel to VB not (VBA)
Sorry mis-stated the question. Is there any difference in quality of graphing when you translate and excel into a VB app? Kind regards, Mark I guess it would depend on how you will create the charts with VB. One cannot just 'translate' XL workbook(s) into VB. Different platforms, different languages, different purposes. -- Regards, Tushar Mehta www.tushar-mehta.com Multi-disciplinary business expertise + Technology skills = Optimal solution to your business problem Recipient Microsoft MVP award 2000-2005 In article <d5q1o1$4vb$1@reader01.singnet.com.sg>, mark...

Setting the Startup Options for an Access 2007 database using VBA
Afternoon everyone, I'm trying to use the information from an MSDN page of roughly the same title (Setting Startup Options Programmatically in Access 2000, http://msdn.microsoft.com/en-us/library/aa140020(office.10).aspx) to setup the startup options of my database. Firstly, I don't know whether this option should be placed in a module or event (like Form_Load). Secondly, I'm not too sure that I have the code correctly written down: Private Sub Form_Load() Dim dbs As CurrentProject Set dbs = Application.CurrentProject dbs.Properties....

Named Cells In Worksheet
This is probably really dumb: After assigning a name to a cell or range of cells; how do you un-name or delete the name? TIA Fred Hi From menu select Insert.Name.Define Select the name from list - Delete Arvi Laanemets "Fred Yager" <fredyager@koolsturgis.com> wrote in message news:06b201c39314$68e716f0$a001280a@phx.gbl... > This is probably really dumb: > > After assigning a name to a cell or range of cells; how > do you un-name or delete the name? > > TIA > > Fred Use Insert, Name, Define. Then, highlight the name and press the Delete button...

Values
I would like to add 2 values (value and percent) to my column bar chart. I need to see my raw N and the percent on one bar. Anyone know how to do that? As an example, set your data up as follows assuming the below: (1) The letters are in column A (2) The numbers are in column B (3) The percentages are in column C a 4 25% b 3 19% c 4 25% d 5 31% Create a Clustered Column Chart. Double-click on the columns and in the Format Data Series dialog box go to the Data Labels tab and check “Label contains Value”. When complete, you should have data labels for all of the bars. Again, in the...

vba excel 2007 data mining
hi, i tried recording a macro to do an analysis using excel 2007 data mining addin. But later when i try to run the macro it did not work. I checked the macro code - it did not seem have any code to call the data mining algorithm in the data mining addin. Please help ...

Cancel button's ActiveControl reflects as value of text box
Hi All, On my form I have an "on exit" code to test whether its text box is "empty" or not. When I click the "Cancel" button I still get the msg that the txt box is empty. Using Debug.print to show the Me.ActiveControl gives me the value of the preceeding txt box. What I'm trying to achieve is to ignore the "empty" test if cancel has been selected by exiting the "on exit" before the msg box sub & clearly I'm misunderstanding the usage here somewhere. Hugh I'm a bit unclear what you want to accomplish... ...

number to name
I want to do my schedule with Excell, and I was wondering if it's possible to type a number and get a name instead... for example if on the 3rd of february, Mark is working, and that I associate the number 1 to Mark. is there a way that if I type "1" under the 3rd of february, that converts it directly to Mark? thanks Gaetan Bart, Using event code you can do it Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B2:H10")) Is Nothing Then With Target ...