How To Make External Data Path Relative |
How To Make External Data Path Relative - Excel |
|
I have tried several ways to make my external data path relative rather than absolute without success. Does anybody know a way to do this i.e. I have used the Data, Import External Data route but when I try to refrsh the data on another users PC (where he has access to the same drive but it is mapped to another drive letter) the refresh will not work.
I'm sure I'm misssing something very simple but it is driving me mad and I'm going on holiday in 1 hours time and would really like to fix this before I go!
I'm sure I'm misssing something very simple but it is driving me mad and I'm going on holiday in 1 hours time and would really like to fix this before I go!
Excel VBA Course - From Beginner to Expert
200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
(80% Discount Ends Soon!)
Similar Topics
Workbook A has a cell that gets data from workbook B as an external link.
It does this using the indirect function because it needs to concatenate the
path and filename from other cells.
Problem is that if workbook B is closed, the cell in workbook A shows #REF!.
So can indirect work using closed external files or must the external files
be opened?
Is there a clever way of making that work?
Thanks!
It does this using the indirect function because it needs to concatenate the
path and filename from other cells.
Problem is that if workbook B is closed, the cell in workbook A shows #REF!.
So can indirect work using closed external files or must the external files
be opened?
Is there a clever way of making that work?
Thanks!
Hi all,
The following code is placed in workbook 'A' and is used to open workbook 'B'. These workbooks will now always be housed in the same directory and i want to change the code to use a relative path reference by determining the path of workbook 'A'. here's what I had:
Sub income_statement()
Application.WindowState = xlMaximized
Application.Workbooks.Open "C:\Documents and Settings\.....\workbook B.xls", UpdateLinks:=xlUpdateLinksAlways
End Sub
could you suggest how to change this to use a relative path reference?
The following code is placed in workbook 'A' and is used to open workbook 'B'. These workbooks will now always be housed in the same directory and i want to change the code to use a relative path reference by determining the path of workbook 'A'. here's what I had:
Sub income_statement()
Application.WindowState = xlMaximized
Application.Workbooks.Open "C:\Documents and Settings\.....\workbook B.xls", UpdateLinks:=xlUpdateLinksAlways
End Sub
could you suggest how to change this to use a relative path reference?
I have a excel spreedsheet that contain external data. I would like to put a
button in the excel sheet to update the sheet without doing right click and
refresh.
My user here are very dummies.
button in the excel sheet to update the sheet without doing right click and
refresh.
My user here are very dummies.
Hello,
Please help!!!
We have a client who will pay using the 1/4, 1/2/ and 3/4 of an hour increments as follows:
From the hour mark to 6 minutes = 0 time paid
Over 7 minutes to 15 minutes = 1/4 hour 15
16 minutes to 30 minutes = 1/2 hour 30
30 minutes to 45 minutes = 3/4 hour 45
46 minutes to 60 minutes = one hour.
They work on shifts and the operation is 24 hours. Each gets 30 minutes for breaks which is not paid. Pay is biweekly and overtime is anything over 40 hours. Currently, the template we are using looks like this (sorry, I could not figure out how to paste it so you can see it in Excel layout):
WEEK 1 Sunday Monday Tuesday Wednesday Thursday Friday Saturday
5/16 5/17 5/18 5/19 5/20 5/21 5/22
TIME IN ENTER TIME, INCLUDE AM OR PM
TIME OUT
TIME IN
TIME OUT
TIME IN
TIME OUT
TIME IN Total Hours First Week
TIME OUT
HOURS WORKED-OR- choose one
*HOLIDAY WORKED
PAID HOLIDAY (not worked)
*OTHER PAID HOURS
EXPLANATION OF *HOLIDAY WORKED HRS OR *OTHER
I am very new to Excel and need help creating a formula that will allow us to calculate this timesheet? I am in desperate need for help.
Thanks..
Please help!!!
We have a client who will pay using the 1/4, 1/2/ and 3/4 of an hour increments as follows:
From the hour mark to 6 minutes = 0 time paid
Over 7 minutes to 15 minutes = 1/4 hour 15
16 minutes to 30 minutes = 1/2 hour 30
30 minutes to 45 minutes = 3/4 hour 45
46 minutes to 60 minutes = one hour.
They work on shifts and the operation is 24 hours. Each gets 30 minutes for breaks which is not paid. Pay is biweekly and overtime is anything over 40 hours. Currently, the template we are using looks like this (sorry, I could not figure out how to paste it so you can see it in Excel layout):
WEEK 1 Sunday Monday Tuesday Wednesday Thursday Friday Saturday
5/16 5/17 5/18 5/19 5/20 5/21 5/22
TIME IN ENTER TIME, INCLUDE AM OR PM
TIME OUT
TIME IN
TIME OUT
TIME IN
TIME OUT
TIME IN Total Hours First Week
TIME OUT
HOURS WORKED-OR- choose one
*HOLIDAY WORKED
PAID HOLIDAY (not worked)
*OTHER PAID HOURS
EXPLANATION OF *HOLIDAY WORKED HRS OR *OTHER
I am very new to Excel and need help creating a formula that will allow us to calculate this timesheet? I am in desperate need for help.
Thanks..
Hello,
I'm pretty new at VBA and was wondering if you could help me out on this:
I have created a VBA userform but will need to have it used by at least 5 users. My question is, can it be done with all users working at the same time and when saving their work all data entered will go to one master excel sheet? if yes, would you be kind enough to share the code?
MS Access is not an option for me so I was wondering if you could help me do this in excel.
thank you so much and would really appreciate to hear from anyone soon.
I'm pretty new at VBA and was wondering if you could help me out on this:
I have created a VBA userform but will need to have it used by at least 5 users. My question is, can it be done with all users working at the same time and when saving their work all data entered will go to one master excel sheet? if yes, would you be kind enough to share the code?
MS Access is not an option for me so I was wondering if you could help me do this in excel.
thank you so much and would really appreciate to hear from anyone soon.
I have a VBMacro Excel file loaded on a Server that numerous people access. A Macro in this file creates a Copy of a specific Sheet within the Active Workbook and I want to Save it to the individual's Desktop.
How do I find out what the current User's desktop folder path is each time the Marco is run by a different User?
Example User's path: 'C:\Documents and Settings\jfarc\Desktop'
Where 'jfarc' is the name of the current User which, will of course change with every different User that runs the Macro.
Also, is there a way to pull out of Excel what is the current User's 'Options | General | Default File Location' entry? Which may differ from the above directory.
I am familiar with and use the following coding for Opening/Saving files to the current directory of the opened workbook, but it only gives the path of the existing Excel workbook and not the current User's Directory Path:
Dim wbThis As Workbook
Set wbThis = ThisWorkbook
ChDir wbThis.Path
How do I find out what the current User's desktop folder path is each time the Marco is run by a different User?
Example User's path: 'C:\Documents and Settings\jfarc\Desktop'
Where 'jfarc' is the name of the current User which, will of course change with every different User that runs the Macro.
Also, is there a way to pull out of Excel what is the current User's 'Options | General | Default File Location' entry? Which may differ from the above directory.
I am familiar with and use the following coding for Opening/Saving files to the current directory of the opened workbook, but it only gives the path of the existing Excel workbook and not the current User's Directory Path:
Dim wbThis As Workbook
Set wbThis = ThisWorkbook
ChDir wbThis.Path
Hi there,
I have a piece of code called ConvertDates that formats data contained on 6 worksheets. The 6 data sheets all contain a data connection to a website of foreign exchange tables.
What I want is for my code to execute as soon as the data connection refresh has finished.
When I use the statement
Code:
The code executes the macro whilst the refresh is still happening, thereby screwing up my results. I don't really want to use a timed wait, because the refresh speed is going to vary from user to user.
Is there some way I can tell excel to wait till the refresh has finished and then execute the code?
Any help would be hugely appreciated.
I have a piece of code called ConvertDates that formats data contained on 6 worksheets. The 6 data sheets all contain a data connection to a website of foreign exchange tables.
What I want is for my code to execute as soon as the data connection refresh has finished.
When I use the statement
Code:
ActiveWorkbook.RefreshAll Application.Run "Project1.xlsm!ConvertDates"
The code executes the macro whilst the refresh is still happening, thereby screwing up my results. I don't really want to use a timed wait, because the refresh speed is going to vary from user to user.
Is there some way I can tell excel to wait till the refresh has finished and then execute the code?
Any help would be hugely appreciated.
Hi,
I made a simple worksheet that we have been manually entering the time for employees, but there are too many errors (even with simple math). Can someone help me convert the time of ex: written 8-4:30 or 10-3 (meaning 8:00am to 4:30pm) where you have the total weekly hours? Right now I have a column for overtime. Is there a way to automatically calculate the over time also? The work day is 8-4:30 with a half hour lunch (lunch is not calculated into the hours, so you minus a half hour). 8-4:30 equals 8 hours. So, if a person works 8am to 6:30pm, how can I set it up where in the first row of time, I will type in 8-6:30pm. In the totals column to the far right, it will display 8 hours. In the (O.T.) column, it will display 2 hours.
This way, when I call in my payroll, I will have the total "regular" hours in one row and underneath, I will have the total overtime hours.
Or... does anyone have a better solution to keeping track of their hours?
Thanks!
Marty
I made a simple worksheet that we have been manually entering the time for employees, but there are too many errors (even with simple math). Can someone help me convert the time of ex: written 8-4:30 or 10-3 (meaning 8:00am to 4:30pm) where you have the total weekly hours? Right now I have a column for overtime. Is there a way to automatically calculate the over time also? The work day is 8-4:30 with a half hour lunch (lunch is not calculated into the hours, so you minus a half hour). 8-4:30 equals 8 hours. So, if a person works 8am to 6:30pm, how can I set it up where in the first row of time, I will type in 8-6:30pm. In the totals column to the far right, it will display 8 hours. In the (O.T.) column, it will display 2 hours.
This way, when I call in my payroll, I will have the total "regular" hours in one row and underneath, I will have the total overtime hours.
Or... does anyone have a better solution to keeping track of their hours?
Thanks!
Marty
Hi all, the title pretty much says it all, I have a form where I am trying to create a button that will pop up a window where the user will browse to the desired folder and once that folder is selected, the path will be copied as a text string into the cell adjacent. My command Button cell is in B3 and the path should be shown in C3.
Can anyone help?
Thanks
Can anyone help?
Thanks
This is my first post in these forums.
I am trying to create a formula which will look at the data in columns A - I (50 rows) and copy this data to columns K - S but ignoring any blank cells. Thereby consolidating the data in the upper rows with no spaces.
I am sure there is a relativley simple answer but it is driving me mad!
Hope you "Guru's" can help
Thanks
Added example spreadsheet to aid assistance.
I am trying to create a formula which will look at the data in columns A - I (50 rows) and copy this data to columns K - S but ignoring any blank cells. Thereby consolidating the data in the upper rows with no spaces.
I am sure there is a relativley simple answer but it is driving me mad!
Hope you "Guru's" can help
Thanks
Added example spreadsheet to aid assistance.
I am creating a basic accounting spreadsheet and I ask for assistance to automate data entry posts. I would like to have entries that are entered in the general journal automatically post to the corresponding ledger accounts. The entries need to remain in chronological order. Relative cell referencing will not work because the rows in the ledgers will not correlate to the rows in the journal because the journal but the entries in the ledger need to input on the next available row in that ledger account. Additionally, as the number of entries increase, can the ledger accounts add a new row?
Attached is a very simple example with three transactions. I have included an end state view in the ledger accounts tab.
I have a graph with various information in it. One is a line graph that tracks hours used in that department per month. The problem is that when it gets to the last month, and there is not yet data for future months, the line goes down to Zero (leavin this big diagonal line that makes the data look funny). I need this line to stop on the last month there is data for. (I do not manually enter the data, it is a formula that I do not wish to delete.) I know I can manually move the data that the line is pulling to make it stop on the last month, but I have a graph for 36 different departments and that's a pain to have to manually adjust 36 graphs each month. Is there a way to tell the line to stop if there is no data? Thanks!!
Hi! I am new to Excel and I am creating fillable forms in excel which are protected so that users canonly input data in certain cells. I am using Excel 2003. The users told me that they do not want text to go beyond the edge of the page when they are inputting data. They also want the words to wrap when they reach the end of the sentence. I am also using check boxes(yes/no) and Ifind it difficult to make the adjacent cells fillable only Thanks for your help and correct me if I am wrong as I am new to this board.
I've been poking around trying to figure this out but can't. The time of 9 hours, 15 minutes is being returned from a calculation but I want the cell to display the 9.25 hours instead. This seems like something simple on the surface, but apparently it's not. Any help would be appreciated!
Hi,
I have a long list of data validation. I was wondering if there is a way when I clik on the drop down menu and type the first letter, automatically Excel shows all list with that letter.
Right now, I have to click on the drop down menu and scroll manually until I reach that desired list.
Thanks.
I have a long list of data validation. I was wondering if there is a way when I clik on the drop down menu and type the first letter, automatically Excel shows all list with that letter.
Right now, I have to click on the drop down menu and scroll manually until I reach that desired list.
Thanks.
I am using XL2007 and have a macro that refreshes microsoft query connections. The issue is the refreshes only happen if you step through the macro using the debugger. When you run the macro normally, everything else functions properly, but the data is not refreshed.
Any help is appreciated. Here is an excerpt of the code:
Workbooks.Open Filename:="C:\Profile.xls"
Sheets("SELECTION").Select
Range("F3").Value = SNR
' THIS PART ONLY WORKS IF YOU STEP THROUGH THE MACRO USING THE DEBUGGER...IF YOU RUN MACRO NORMALLY THE CONNECTIONS DO NOT REFRESH
ActiveWorkbook.Connections("Connection").Refresh
ActiveWorkbook.Connections("Connection14").Refresh
ActiveWorkbook.Connections("Query from C_Profile").Refresh
ActiveWorkbook.Connections("Query from C_Profile1").Refresh
Any help is appreciated. Here is an excerpt of the code:
Workbooks.Open Filename:="C:\Profile.xls"
Sheets("SELECTION").Select
Range("F3").Value = SNR
' THIS PART ONLY WORKS IF YOU STEP THROUGH THE MACRO USING THE DEBUGGER...IF YOU RUN MACRO NORMALLY THE CONNECTIONS DO NOT REFRESH
ActiveWorkbook.Connections("Connection").Refresh
ActiveWorkbook.Connections("Connection14").Refresh
ActiveWorkbook.Connections("Query from C_Profile").Refresh
ActiveWorkbook.Connections("Query from C_Profile1").Refresh
I have an excel 2007 file sitting in a shared network folder. I only want one user to be able to make changes at a time (any other users would get a read-only). For some reason it currently does not do this, and I have multiple users with the same doc open. I'm concerned that changes will get over-written when 2 people are saving their changes. Can anyone help me with the settings for this.
Hi there,
I have a spreadsheet with some cells setup with a drop-down list containing
Y, N or N/A
This is being used on a TabletPC but if I make a mistake or need to change
back to a blank field I have to invoke the soft keyboard, activate the cell
and hit backspace then close the soft keyboard - quite a long-winded
procedure just to change an incorrect choice!
What I would like to do is add a blank to the list so if I have to revert
back to a blank I can just use the stylus to choose a blank from the chooser
list.
How do I add the option of inputting a blank from the Data Validation List
bearing in mind I am using the Data Validation Source box for entering my
choices directly and not specifying a range of cells?
I have tried adding "" and even a space to no avail.
Although not a betting man I would wager there is a simple 'fix' for this
but things are only simple if you know how in the first instance! ;^)
--
Thanks & regards,
-pp-
I have a spreadsheet with some cells setup with a drop-down list containing
Y, N or N/A
This is being used on a TabletPC but if I make a mistake or need to change
back to a blank field I have to invoke the soft keyboard, activate the cell
and hit backspace then close the soft keyboard - quite a long-winded
procedure just to change an incorrect choice!
What I would like to do is add a blank to the list so if I have to revert
back to a blank I can just use the stylus to choose a blank from the chooser
list.
How do I add the option of inputting a blank from the Data Validation List
bearing in mind I am using the Data Validation Source box for entering my
choices directly and not specifying a range of cells?
I have tried adding "" and even a space to no avail.
Although not a betting man I would wager there is a simple 'fix' for this
but things are only simple if you know how in the first instance! ;^)
--
Thanks & regards,
-pp-
This formula should be very often to use, but i cant found it anywhere, so i ask this here
For example in a1 is :
mike is playing basketball, he is very great player
i want to make it in b1 to be uppercase only in first letter so :
Mike is playing basketball, he is very great player
i know about proper formula =proper(a1) , but proper formula is make uppercase to all first letter in all words, we just need first letter in first words to be uppercase (and to every first words after dot if it can)
For example in a1 is :
mike is playing basketball, he is very great player
i want to make it in b1 to be uppercase only in first letter so :
Mike is playing basketball, he is very great player
i know about proper formula =proper(a1) , but proper formula is make uppercase to all first letter in all words, we just need first letter in first words to be uppercase (and to every first words after dot if it can)
How would I create a system where an excel file is updated in real time with new values? I have a program which logs data, and I want to have excel open, plotting the data in real time. Is this possible?