Open File From Network Drive |
Open File From Network Drive - Excel |
|
I have a workbook located on my local laptop. The workbook contains a procedure that opens some excel source files from a network folder and copies data to the local workbook.
The issue is that the I need to change the drive in order to open the source file and I have no clue how to change the drive from local drive to network.
The folder is not mapped to a drive-letter. I would avoid mapping the folder, since a other users use a same workbook with the same code on their laptop assessing the same files.
Could anyone help me on this.
ChDrive ???
ChDir sFileDirectory
Thanks,
Bubo
Similar Topics
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("H10").Value & Format(Date, "mmdd")
This saves it as the correct cell value + the date, but it saves it to my documents. I need it to save to a shared folder on a network drive.
Any help would be greatly appreciated.
THANKS
I have a number of different files that I often need to run a macro on. In order for me to do it on the 75-100 files I have at any given time, I need to open one, run the macro, close and save, then open the next one.
Is it possible to write a macro that will start with the first file in a folder, open it and update links, run a macro, save and close, and open the next file in the folder until it has open all the files in the folder.
I have experience with creating macros that reference different workbooks, but not sure how to go about opening files with different filenames (without referencing the exact filename).
I'd like to be able to have basic code for opening, saving and closing, opening next file, saving and closing, etc. and input the macro I'd need to run in each file in the appropriate location. Is this possible? Any help is greatly appreciated!!
Thanks,
Jason
We all know we can change the source of a link of the workbook by clicking Edit>Links>Source>change source but that will change the source of all workbook formulas which are linked to a certain file, what about if i need to change the source of a single sheet? is this possible?
I appreciate your support.Thank you !
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
People need to open the file to be able to sign up for various duty rosters.
We would like for the file to open for the first person.
And then for any others after that, get a message that the file is in use
WITHOUT the option to open a read-only copy.
Our staff can't read and they keep opening additional copies of the file!
I have read about sharing the file and I don't think that would make things
any better.
Thanks,
Lynn
Make sense? I'm a bit confused myself. Maybe just a list of basic workbook-switching techniques or commands would be useful.
- Michael
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?
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!
My problem was that a userform defined with Excel at work (containing DT pickers) gave the message in the title when opening it at home. I had a light-bulb moment and wondered whether there was a difference in the version numbers for MSCOMCT2.OCX at work and at home. Turned out the work version was newer. I then copied the MSCOMCT2.* files from work, made a backup of them at home and copied those from work to my C-drive (Windows XP - c:\windows\system32\ ).
No luck. I then rebooted the machine - still no luck.
Then, finally I unregistered the old DLL via
Code:
regsvr32 /u c:\windows\system32\MSCOMCT2.OCX
(not sure if this was necessary, but I didn't think it could hurt). Reregistered the DLL via
Code:
regsvr32 c:\windows\system32\MSCOMCT2.OCX
and what do you know - it worked.
Summa summarum - it could be an idea to check whether the two machines have different version numbers for the MSCOMCT2.OCX files.
Sub OpenPDF() 'Dim pdf As String On Error Resume Next 'pdf file to open pdf = "K:\PDF\mypdf.pdf" 'open the pdf file ActiveWorkbook.FollowHyperlink pdf End Sub
So then I tried to create an instance of acrobat by setting a reference to the acrobat object but I can't get this to work either!
The code I'm using is Code:
Sub OpenPDF() Dim pdf As AcroPDDoc Dim strPDF As String Set pdf = CreateObject("AcroExch.PDDoc") 'pdf file to open strPDF = "K:\PDF\mypdf.pdf" 'open the pdf file pdf.Open strPDF End Sub
Any ideas what could be wrong with either approach?
Thank-you
I need to open a password protected workbook using VBA.
I've tried the code below but I still get prompted for a password.
Code:
Workbooks.Open Filename:="\\HOME\Working\Report.xls" _ , Password:="xxxx"
How do I get it to open automatically???
I am working with an Excel spreadsheet and saving it as a .csv file in order to upload to an application that parses out the .csv data as transactions. The system requires .csv files, so this is how I need to save my doc (with this extension). I have been successful at preventing Excel from coverting that long number into scientific format. I have saved as a TXT file, pasted the longer number and it displays correctly. That is all good. But I have to save as a .csv. So if I do that, close the Excel window, and then open again (as the .csv file), the numbers are back to being displayed in scientific format. I have tried creating an Excel doc from scratch and entering text in Text format, to see if this created a cleaner file. But again, the second I save as .csv, close the window and then open that file up again, that dang scientific format is back.
Does anyone have any idea of how to work around this? Once I have successfully gotten the numbers to display as the long-chain number, how can I get them to "stick" so that they don't revert back to scientific format when I reopen the file?
Thanks so much for your help!
But I did so in a beta file (test file). The real file has become so big (103MB!) that Excel cannot even open it anymore! The file contains archive info that we do not have anywhere else.
Is there anyway then to open the file or to reduce its size without opening it (through magics...)? I just honestly don't know how to retrieve that info before deleting that file.
Thank you very much for your help.
Am in need for this solution very badly and what could be a better place than excelforum !
I have an MS Excel File (2007 version) sample file attached, which has name, designation, blood group and so on. The last column is for hyperlinking photographs of individuals.
In the same folder where I have saved this excel file, are lying photographs of individuals. While scanning the photographs, I have saved them serially i.e. 1,2,3 and so on.
In the Excel file, in last column, I have given the respective serial numbers. In order to hyperlink one has to select that particular Cell, press Ctrl K and you automatically go to the folder containing individual photographs, you select that photo and OK.
Problem :
I have to do this hyperlinking one by one and if there 1000 photos, lot of time is wasted.
Solution Needed :
Just in case of excel formula, which we copy and paste, Can I get a command by virtue of which the column titled Photo or column next to it gets automatically Hyperlinked to respective photo WHEN I copy and paste such command to all cells in that column.
Thanks a million and warm regards ::: Jack
Found the following code and it gets me close, but it copies the cell references, not the values. It also allows me to specify the file name from a cell reference.
I want the new workbook file to simply be saved, not opened, and a message box to display stating where the file was saved (will always be in the same location on the LAN).
What modifications do I make to this to get this to work per above requirements?
Sub CopyMe()
Dim SaveMeAs As String
SaveMeAs = Sheets("Sheet1").Range("B2").Text
Sheets("Sheet3").Copy
ActiveWorkbook.SaveAs Filename:="C:\My Documents\" & SaveMeAs
End Sub
All of the buttons were working fine. Then I saved and closed the workbook and went to lunch. Now when I open the workbook, the buttons don't work! When I click them nothing happens. They appear frozen. They don't even seem to click. No error message. Nothing.
If I right-click the button in Design Mode and select Properties, I get sheet properties not the button properties. I can't seem to locate the command button properties any longer. I still see the button name "cmdButtonGetInfo" and "=EMBED("Forms.CommandButton.1","") in the name box and formula bar. The odd thing is if I create a new button it works fine until I save and close the file. When I reopen the file none of the buttons work.
It's like the buttons are being disabled when I close or open the file. Any suggestions?
Can anyone help?
Thanks
I am copying charts to display the same information for different regions. When I work on the copied region, I find it a cumbersome task to go through each charts source data and change the cell references to the different region. Essentially I am doing a trending analysis for each region, with a region having its own sheet with 5 charts per sheet. What I have done is copy the original sheet and am updating the sheet for another regions source data by going to the source data. The source data is all in the same row/column format, but each regions source data has its own sheet. Is there a more automatic way to do this? Perhaps a way to update all 5 charts references at the same time.
I've been getting this error on occasion recently.
- It's a shared document
- There is usually 10-12 people using the file at the same time
I've read on the Microsoft Help site that the issue is that somebody is accessing the file while another is trying to open it.
Is there a way, perhaps through VBA, that I can not allow a user to save while somebody else is opening the document? Other suggestions on how to avoid this error?
Thanks!