Using Pathname In A Variable To Activate
Sep 29, 2013
In one Sub() I assign a pathname to a variable. At the moment I am using this to activate the window of a file that is open....
Code:
Windows("OLD LOAD LIST.xls").Activate
But I would rather use this as the file name may sometimes be different...
Code:
Windows(Old_File).Activate
But it isn't working. Is it because I set the Old_File variable outside this Sub() and the variable is losing its contents ? If so, how do I make the variable keep its contents until its changed ?
View 9 Replies
ADVERTISEMENT
Oct 17, 2012
In VBA I need to open a workbook using the UNC pathname. Right now the code is:
mfdate = FileDateTime("HG Equipment Tracking Matrix.xls")
I need to add the UNC pathname to the file name. Nothing seems to work.
The UNC pathname is: eavertn-nas-06 wikle
What is the right code to make this work?
View 1 Replies
View Related
Nov 9, 2011
I want to have a macro (because i do so often) that for any active workbook that I'm using will....
capture the full pathname and file name and place into clipboard for pasting elsewhere.
eg run macro and the following is added to clipboard.....C:Test DirectoryTest_File.xls
View 7 Replies
View Related
Jul 22, 2009
I work for emergency services, and one of my colleagues has a GPS camera that snaps digital pictures and collects the coordinates where the image was shot. These coordinates are recorded in a database file readable in Excel. The emergency mapping software can load these points along with the images, but in order for the hotlinks to work (and thus be able to click on a point and load the image), I need a field in the database file that shows the full path name of the respective JPG image. As it is, the database file has a field (named ORIGINAL) that records just the filename of the image.
I need a field that contains the full pathname of the image. This is easily done in Excel, but I need an external script that will run this outside of Excel because I need to streamline the process. If dispatchers need to go into Excel and edit columns to make the data show up, they're not likely to bother.
I have a blank (not populated) column in the dbf (named INTERNAL) that I can use for the full pathname. What I want to do is begin with the text "C:/" and insert the text in the ORIGINAL column immediately following this. Is there a script, such as in a batch file, that will run this outside of Excel?
View 9 Replies
View Related
Feb 1, 2007
I have made a dialog box open using VBA to select some file,
Function FSel()
filetoopen = Application. GetOpenFilename("Document Files (*.xls), *.xls", 1)
Workbooks.Open Filename:=filetoopen
End Function
I need to activate the sheet which is selected in that string "filetoopen".
I am not getting the way to do this ,the normal Windows(" " ).Activate is not working with variable as input.
View 5 Replies
View Related
May 26, 2009
I have created a macro to split out information from one excel workbook into 2 additional workbooks, and format them the way we need. Everything works fine on the filename I originally recorded the macro from, but is giving me a 'subscript out of range' error when trying to run the macro on a file with a different name.
currently, my test file has the 2 following fields that I somehow need to have as variables:
Windows("insmacrotest20090418.xls").Activate
and
Windows("Book1").Activate
I need the filenames and workbooks in the parenthesis to be variable so that the macro can be run on other reports with varying filenames.
View 10 Replies
View Related
Nov 10, 2009
I have an excel workbook that uses a .bmp file [a picture]. The master excel file and the .bmp file are in the same directory on the network. The excel file is "Read-Only", so the user would open it, customize it and save it on their own computer or somewhere else on the network.
They are only allowed to save it under "Save As". Once the user saves it, they would then be able to use their customized copy for future updates etc. The problem is that the .bmp file is missing from this new directory.
Is there a way to automatically save a copy of the .bmp file under the Directory specified by the user, so it's always available when the Excel workbook is opened? Or is there a way to attach the .bmp file to the excel file, so they are always saved together?
View 6 Replies
View Related
Nov 23, 2007
When i exdcute my code in the userform, the line
AppActivate "Microsoft excel"
does not run, so it gives me
Run Time Error 5 :
Invalid proceduer call or Argument
View 9 Replies
View Related
Mar 4, 2009
I have a spreadsheet with several formulas where I have to go into each one of them to activate the calculation. I use F2 and enter. Automatic calculation is on. Do any of you know how this can be done automatically. A VBA-code will fit the purpose.
View 3 Replies
View Related
Apr 23, 2008
I have two or more DIFFERENT INSTANCES of excel workbooks open at the same time. EX: Wk1, Wk2, & Wk3
Currently I have Wk3 showing in the screen.
Through a Macro, how can I display (bring to front) Wk1 without closing Wk3 ?
This is part of a longer macro, so I only need to know how to do above.
View 15 Replies
View Related
Sep 16, 2009
I have an open workbook(A.xls) where the user can press a button which opens another workbook (B.xls) In workbook B they need to add new names then press another button to run another script. The script needs to switch to workbook A in order to work correctly. How can I switch to workbook A without using the name of the workbook? The reason I cant use the name to activate is because the first workbook that is open is not always A.xls
View 4 Replies
View Related
Nov 23, 2009
ActiveCell.Activate. I'm reading through a VBA book and one line of code was
View 4 Replies
View Related
Feb 5, 2010
I'm reading a hyperlink into a spreadsheet using an =index,match,match formula. The Hyperlink appears as the correct one when you read it in the cell, but it does not link to that address....
View 8 Replies
View Related
Jan 19, 2012
I have a cell (B2) in which there is a formula referencing the value of another cell with the purpose of generating a URL- ="[URL] such that when a numeric value is entered in B3, B2 is made to contain the full URL including B3 as a unique identifier.
I have a macro which straightforwardly copies B2 and pastes its value in the same cell, rendering the text value of the URL.
How do I go about activating the URL as a hyperlink in the macro? It seems like if I click in the cell and hit return, or right-click/Hyperlink.../OK I'm creating a macro to reference the exact unique identifier present at the time that I'm recording the macro- e.g. if B3 is "123", I'm setting the macro to set B2 as [URL] rather than the actual unique identifier in B3.
Has anyone successfully activated the content of a cell as a hyperlink dynamically based on its exact content?
View 2 Replies
View Related
Apr 8, 2007
In the following code, I have a find sub... when the user enters a date and hits the "Enter" key, is there a way to bypass the "Ok" key?
Private Sub cmdFind_Click()
Dim ws As Worksheet, myDate
Dim rFoundDate As Range
'check for valid distribution date (between October 1, 2006 thru December 31, 2014)
myDate = txtFindMyDate
With myDate
If DateValue(txtFindMyDate) < DateValue("10/1/2006") Or DateValue(txtFindMyDate) > DateValue("12/11/2014") Then
MsgBox "Please enter a date between October 2006 and December 2014"
.SetFocus
Exit Sub
End If
End With
Cells.Find(What:=txtFindMyDate, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Unload Me
End Sub
View 9 Replies
View Related
Jan 4, 2009
I have 5 Optionbuttons (in userform) named: Sheet1, Sheet2, Sheet3, Sheet4 and Sheet5.
When I open workbook Optionbutton1 (Sheet1) is activated, but what kind of loop(?) I need, if I want activate same named optionbutton than activate sheet.
Example: I activate sheet3 then Optionbutton3 (named sheet3) have to activated also.
I tried simple codes,
First worksheet:
Private Sub Worksheet_Activate()
OptionButton1.Value = True
End Sub
Second worksheet:
Private Sub Worksheet_Activate()
OptionButton1.Value = False
OptionButton2.Value = True
End Sub
View 9 Replies
View Related
Mar 7, 2010
I am getting old and can't remember how to activiate a graph to accept and show new numbers.
Example: In "Charts and Graphs for Microsoft Office 2007" by Bill how does one get the graph (Figure 3.35) to accept data for the coming months and have the data reflected in the Linear Trend Bar (the forecast bar is not in the figure) but I would like to include it?
View 9 Replies
View Related
Apr 6, 2007
1. What's the difference between:
Worksheets("Sheet1"). Range("A5").Select
and
Worksheets("Sheet1").Range("A5").Activate
?
2. Can I use a Selection. method with an object 'selected' using .Activate in the preceding code line?
3. Say I am currently on Sheet2, Cell A5. When I click the macro button, I want Excel to copy the value from Sheet1, Cell A5 and paste it into Sheet2, Cell A5. But I do NOT want the screen display to actually jump from Sheet2 to Sheet1, and then back to Sheet2. So do I need to use .Activate, or .Select, to copy the value of Sheet1, Cell A5?
View 4 Replies
View Related
Jun 4, 2007
I have the following code (just pasting the relevant section) which crashes when it reaches the highlighted line of code. and a dialog box pops up with the text: "Object variable or With block variable not set"
Sub test()
Dim StartRng As Range
Dim Buffer As Range
Set StartRng = WorkSheets("Sheet1"),Cells(1,1)
StartRng.Activate
ActiveCell. CurrentRegion.Select
Buffer = rngStart.CurrentRegion.Copy
' I also tried the following line of code but that didn't work either
'Set Buffer = rngStart.CurrentRegion.Copy
..
...
End Sub
View 9 Replies
View Related
Sep 17, 2012
I have a bunch of array formulas that need to be activated by going on the cell, hitting F2, then hitting Ctrl+Shift+Enter. In the attached sheet I have done a record macro to automate this. I have another sheet with something similar just a lot more items on Sheet1 (~250 items). It will be very time consuming for me to record macro and hit F2, then hit Ctrl+Shift+Enter ~250 times so I am wondering if there is any way to activate all the array formulas on the sheet in one go using 1 formula. I'm not too concerned with file size.Also, not all arrays only include columns A and C, other columns (D, G, H and J) are included too.
View 3 Replies
View Related
Dec 14, 2006
Is there a way to activate a Macro with a conditional in one of the cells? It's like this, I want that if the content of A2 changes to "True" then C2,D2 and E2 change to bold and the background color change to yellow. Is there a way to do this?
View 14 Replies
View Related
Nov 2, 2007
Learning VB6 by trial and error from code snippits on the web, however, am embarrassed to say I can't solve this simple one:
Trying to Open an Excel sheet and read it from VB6 code written in another Excel file.
View 12 Replies
View Related
Jun 3, 2009
What is the quickest way to eliminate .Select, .Selection and .Activate from code to increase efficiency?
How do I refer to the range and workbook without selecting them first?
View 14 Replies
View Related
Sep 6, 2005
Consider using a Worksheet Calculate Event macro (not Change Event) to
monitor the cell in question and call your macro when conditions are right.
"coal_miner" wrote:
> Greetings. Is there a way you can activate a macro through a formula.
> Example:
> =if(A1=B1,(macro here),"")?
View 14 Replies
View Related
Jan 1, 2010
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z$29:Z$39)-1)
Above is the formula that I have, I am putting it into Column L. When I do auto fill every row that has data in J and K the formula goes into as you would expect.
However the formula does not activate until I double click inside each individual row and hit enter. Therefore if the calculation in L4 was 1.0, then 1.0 will appear as the calculation in every row of Column L until I double click in each row individually and press enter.
View 3 Replies
View Related
Oct 11, 2011
How do I activate four cells to the right of A5 using the offset function. I will need this to operate indivitually for each row so I cant use Range("B5:E5").Select. All I want to do is to merge the four cells to the right of column A
View 6 Replies
View Related
Aug 3, 2012
I want a macro to be activate ONLY when I check the checkbox. Right now, it activate the macro each time I checked the box.
I want the macro to be activate ONLY when I put the check sign in the checkbox. Is that possible?
View 2 Replies
View Related
Mar 27, 2013
I have a path to a folder that is already running. I just want to bring it up on the screen. Would that be done with AppActivate?
path:
"xxxxxLoanImportFileLoadingDockXXXXXSplitter"
Dim TargetDirectory as String
TargetDirectory = "xxxxxLoanImportFileLoadingDockXXXXXSplitter"
Looking for correct syntax.
View 3 Replies
View Related
Oct 3, 2013
How do I use VBA to Activate Sheet1 of a workbook? I am using the following to go BACK a sheet, but really need to get to the first sheet in the workbook.
Code:
Sheets(Sheets.Count - 1).Select
If it's a new wb, then it's tab is called Sheet1.If it's an existing wb, and it only has 1 sheet, then I need that one (in case someone has added a sheet and deleted Sheet1)
View 3 Replies
View Related
Oct 30, 2013
What's the difference in *.select and *.activate is?
IE, workbooks("workbookname.xls").activate and workbooks("workbookname.xls").select.
View 3 Replies
View Related
