I have a file which is kept on the C Drive. Each day, a different excel file is downloaded to a usb memory stick. The first file reads data from this file. The problem is, the drive letter keeps changing from E to F and the macro then crashes out.
The file on the memory stick always has the same name, so is there a macro to find the drive letter of this file and then use it in my existing macro?
I've made a macro that would create some folders on a shared drive.
there is an issue if the path i want to create is too long.
I get this error.
Uploaded with ImageShack.us
is there anything we can do to pass this or any solution for it?
I am trying to allow users to select a file, and then the file name/ the file path / and date are added.
Right now, all those functions work in the following code, but the file path shows up as a letter drive. As there could be upwards of 50 people using this file and the mapped drive could be different, i wanted the full path to be displayed.
Code:
Private Sub File_1_overwrite_Click()
Dim filename As Variant
Dim filename1 As Variant
filename = Application.GetOpenFilename(, , "Select Programme")
filename1 = filename
filename1 = Mid(filename, InStrRev(filename, "") + 1)
File_1_link = filename
file_1_name = filename1
File_1_date = Format(Date, "MM/DD/YY")
Excel 2002 SP3
When a file is currently open, and the user selects "Save as", it takes them to the files native directory. (Where it was last saved).
If the user has that same file open, opens another file from a different directory, goes back to the first file and selects "Save as", it takes them to the location where the last file was opened from.
information about users selecting a folder at this link. However, how do I change the starting point to a network drive, as opposed to "My Computer"?
For example, we reference our network drives like this:
\OurSite.comSharedFoldersDivisionName
In Windows 98 for example when you created a Customised Toolbar and attached macros to it a file was created with the user name followed by 8.
i.e. timbo8
You could copy this file and keep it was a backup in case you lost the toolbar/s. Which invariably happened whn someone else logged onto your pc. When the company transferred over to Windows 2000 this file was called something else I thing it ended in a .xla extension but I don't know what it was called or where it it located.
I am looking for a VBA Code which will:
1. Search for a specific File.
The file will the same file (ABC.xls) BUT the user might want to change it by typing a new name & Extension in the first Inputbox.
2. A second InputBox (Drive & Path) should now Pop-up.
If it will be left empty - the search should take place throughout ALL the installed HDs.
If the user decides to minimize the search range he/she will type a Drive & Path, the search should reduce/constrain itself to the specific Path ONLY.
My task is to show its Path location in a MsgBox (or to show "file not found")
It might be a good idea if the code can be extended beyond presenting the Path to:
1. Open the Windows "Explorer" (or "My Computer") in the specific Path with the file being selected (marked).
2. Opening that file, in "Excel", upon finding it.
A 3 section, VBA, code will be more than appreciated.
is it possible to configure Excel in order to save the Hyperlink path for a file with absolute path and non relative?
I notice that the hyperlink is ....pdf ry.pdf
if I change the position of the file excel there is a problem!
I would like to save es. d:invoichepdf ry.pdf
I have a variable ("DestFile") that defines a path to a file (used in saving the file)...
I'm in the process of getting a Sub to hyperlink to this file, but in some circumstances, I may only want to hyperlink to the folder, not the actual file...
How would I go about trimming the "DestFile" address to get a "DestFldr" address?...
An example of "DestFile" might be;
S:BryanFor KenGulf ConstructionST0609014-t.xls
(the file name length may vary)
What code can I use to consistently trim it back to;
S:BryanFor KenGulf Construction
as the "DestFldr" variable?
I want to run a macro to separate File Name and Path from the given complete path
For Example
Code:
In Column A : Given Complete Path " C:MainFolderRecordsSubFilesFile1Record.pdf
I need it separeted like
Code:
In Column B :File Name = Record.pdf
In Column C :File Path = C:MainFolderRecordsSubFilesFile1
Is there any way to do this through a macro
I have the following code (that I borrowed) and was using it to export some worksheets to a new workbook. It worked fine for about 4 times, now it says it can't find the file path and stops on the "MkDir FolderName" line.
View 2 Replies View RelatedI want to write a macro that would go to a certain drive and search for a file?
View 9 Replies View RelatedIf you don't know what the drive letter will be, because on other people's computer it could be different than what you have.
I only have the folder address
\ROGERSDAILYREPORTSXLSDM
I made an excel sheet that links to other sheets on a different drive. It works while I am making it, but upon save and reopen all the hyperlinks produce this error: The address of this site is not valid. Check the address and try again.
Auto Merged Post Until 24 Hrs Passes;Figured it out. File, Properties, Summary tab, Hyperlink Box to base Drive. Auto Merged Post Until 24 Hrs Passes;Figured it out. File, Properties, Summary tab, Hyperlink Box to base Drive. Auto Merged Post Until 24 Hrs Passes;Figured it out. File, Properties, Summary tab, Hyperlink Box to base Drive. Figured it out. File, Properties, Summary tab, Hyperlink Box to base Drive.
I am trying to move and rename an excel template file using a macro. My code works fine when it is moving the file locally on my C: drive. However, when I try to do it on a mapped network drive I get a Path/File access error. Here is the relevant code:
[Code] ......
I tried it both ways that are commented out - both give me the error. I have permissions to read/write in all relevant folders. What am I missing here?
I need code to print a file located in my C drive.
View 6 Replies View RelatedI want to open a specific sheet and refresh only said sheet when i open another sheet for example x.xls
So opening x.xls will automatically open y.xls
I've tried this in the workbook code area but it doesn't do anything.
[Code] .....
I am trying to import some csv files so I can combine them, but am having probs with the filename and location.
Sub test()
Dim wsName As String
wsName = ActiveCell
Sheets("Data").Select
With ActiveSheet.UsedRange
LastRow = .SpecialCells(11).Row
End With
With ActiveSheet.QueryTables.Add(Connection:="TEXT; &thisWorkbook.Path &" " & wsName &", Destination:= Range("A" & LastRow))
.Name = wsName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells.....................
How to download the zip file from internet explorer and save it into c: using VBA codes.
I have tried many codes from the internet but none of them are working. i am facing problem for the popup box. the macro which i have found was not supporting to it.
Please find the source data from my web page.
[Code] ....
I'm looking for enabling an excel file with a password that prevent it to be copied on an another drive.
View 1 Replies View RelatedI am having trouble creating a hyperlink to a place in the current document for a file stored on a network drive. My workbook has a lot of sheets (50 or so), so I created a 'Navigation' sheet that contains hyperlinks to all the worksheets located therein, and pasted a link back to the Navigation sheet in cell A1 on every tab.
Setting up these Navigation sheets has never a problem until I started posting the file on a network drive. As soon as I did that, I noticed that the internal hyperlinks on all sheets in the workbook would stop working after I closed the file in Excel. Note that this network drive is not set as a drive letter on my computer, and is only accessible by entering the server ip address in Windows Explorer.
An example address the hyper is trying (and failing) to open is: "file:///\ IP ADDRESS sub-folders activefile.xlsm"
I've tried a few options, using the HYPERLINK formula, using the HYPERLINK feature in the Insert ribbon, etc, but all to no avail.
I want to list file names from a directory and sub directories and hyperlink them. I have been able to list the file names onto an excel sheet, but I cannot get it to hyperlink to the file.
'Force the explicit delcaration of variables
Option Explicit
Sub ListFiles()
[Code]....
How to save a file, with file- name. but the directory is to be read in worksheet "towns" in Cell1 (brussels) and filename in worksheet "names" in cell B2 (i.e. winter), so it saves to c:russelswinter.txt as a wordpad or kladblok txt file, that keeps a number, so each time we push a button "go back from worksheet names to worksheet towns" the "number" that is saved in the txt document goes up by value +1. In Flemisch, the "old" code goes as follows, and saves the number in the txt file Factuurnummer7.txt. But I want that the file name (here: FactuurNummer7) can be a variable text issue, which has to be read - as already noticed - in cell B2 (with the word WINTER). So the are 2 worksheets: towns, ans names
pad$ = Application.DefaultFilePath
'controle = Dir(pad$ + "FactuurNummer7.txt")
'If controle = "" Then GoTo EerstAanmaken
'Open pad$ + "Factuurnummer7.txt" For Input As #10
'Input #10, Nummer1
'Close #10......................
Below is the current code I have for File Copy before the workbook closes. This file will be distributed all over and obviously will not have the same old path and new path locations as I have in my code also will not have the same file name. Is there anyway to still perform the file copy without knowing the old path and file name and possibly have message box pop up to ask the copy to location and use that in the new path string?
Sub Macro1()
Dim fs As Object
Dim oldPath As String, newPath As String
oldPath = "I:EXLDATAMC Daily" '<---Where the file is currently located
newPath = "H:South RegionOrlando Mail Services2008DI" 'Since the super shared drive is super slow we will just copy and replace this file each time before we close and of course after we save
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile oldPath & "" & "OrlandoMail.xls", newPath & "" & "OrlandoMail.xls"
Set fs = Nothing
End Sub
I am currently creating a simple macro to save out files to a specific folder on one of our network drives. Since we will be doing this often I would like to amend the date the the file name saved for sorting/organizational purposes.
Unfortunately I have been running into a few issues, this is what I have tried so far but keep getting a SaveAs error:
Code:
ActiveWorkbook.SaveAs Filename:="vosinsharedClient Implementation TeamDC Tools" &
"Copy DC Conversion WB_2014 " & Format(Date, "yyyymmdd") & ".xlsx", FileFormat:=xlNormal
I have also tried:
Code:
ActiveWorkbook.SaveAs Filename:="vosinsharedClient Implementation Team
DC ToolsCopy DC Conversion WB.2014_" & _
Format(Now(), "YYYYMMDDhhmmss") & ".xlsx", FileFormat:=56
But still no avail. I believe it may have something to do with the file format but I am not sure. I am running Excel 2010.
getting full file path from selected input file?
I have a macro with text box and browse button.
User can click on browse button and it will allow them to select only text files.
Now for further processing I need to get the file path and file name in 2 different variables.
Like if user selected C:/Temp/Test.txt
Variable 1 should have C:/Temp
Varialble 2 should have Test.txt
I just found a code to copy file path and file contents. However it is copying folder path and folder contents.here is my requirement.I will specify a path, macro has to copy that file name in a particular cell, then it has to copy all its contents.
ex:
file name 1 has 3 sub files in it, file name 2 has 2 sub files in it--
column AColumn B
file name1file contents
file name1file contents
file name1file contents
file name2file contents
file name2file contents
And also i have extend this macro to write a index function. i will tell u about this. but first i need above thing I researched but I didn't find.
I need file name in a column then i need file contents in b column.
if a file has 10 file contents in a file then that file name should come 10 times in a column, and in b column all its contents.
Each month I am sent a spreadsheet which contains a list of 100's of different file paths (including file name).
For example (Quater 1 is the document name)
R:ManagementJohn2008SalesSectionAQuater1
I would like to remove the file name from the file path (I will then convert the file paths to hyperlinks which will open the folder the document is stored in but not the document itself.
For example
R:ManagementJohn2008SalesSectionA
I am trying to separate a filename from a filepath in Excel VBA 2003.
I am using the Application. GetOpenFilename command to get the user to select the correct file.
However, this also seems to record the filepath.
I am trying to separate the filename out of this so that each file( name) activates a different sub procedure.
I am storing the result of the getopenfilename as a Variant.
At the moment I am having to specify the full path in order to get each sub to run. This is fine for 1 user - but not for others as the files are stored in different locations for each user.
I'm using this code to import a file, but I want to be able to save just the path (with no file name) to a variable to use for the output file. This code saves the path and file.
FileName = Application. GetOpenFilename _
FileFilter:=Filt, FilterIndex:=FilterIndex, Title:=Title)
Any help would be greatly appreciated. I didn't know but what there was a function that I could apply to the 'FileName' variable that would remove the file name...don't know.