I have created a workbook that imports data from another workbook which is used frequently on a network drive. After I import the data to my new workbook, it locks the original workbook for editing. Is there a property that will allow me to disable this 'locked for editing' read only mode or any other way to get around this?
View 6 RepliesI am importing external data to "Workbook A" from the source workbook, "Workbook B." Workbook A is set up to refresh every 15 minutes. Workbook B is shared. I am recieving an error saying either, the 'file is in use', or 'the file is locked,' depending on the senario.
Scenario 1:
1. I open A and enable data connections.
2. I try to open B, but receive the message: '(File location...) is in use. Try again later.
Scenario 2:
1. I open B.
2. I open A.
3. I go back to B to edit information.
4. When I try saving the information I get the following message: 'File is locked. Try the command again later.'
I need to be able to have A open and periodically refreshing. I also need to allow users unhindered access to B at the same time.
[URL]....
I have 4 workbooks, all of them contain data that is managed by other teams at my company. I need to take all of this data, organize, and concatenate it into one sheet for myself, that I will then reference in other workbooks to various OTHER departments in our company that need the data for certain projects. I have the organization laid out in a lot of very complex formulas so that most of this is automated, and not manual.
I need a solution that can pull data from the 4 workbooks without locking them for use, to allow me to keep my document open all the time, and they can update theirs at their leisure. Everything works fine, until I hit the refresh button. Once this is hit, all of the linked documents are locked and cannot be opened, even in "Read Only" mode. Once I close the master document, the files are free to be opened. I know a workaround for now is that i can not "refresh" the document, but rather close and re open it for changes.
Some steps I have already tried :
1. Create an intermediary file that no one uses, and can be locked all day without a problem. This doesn't work for me since the intermediary file needs to be open to refresh, which locks the original source doc, leaving me in the same place as before.
2.Modify the connection string to display "Mode=Read;" instead of "Mode=Share Deny Read"
3.Create a new connection with "Read" only selected in the Advanced Tab
I have been trying to edit a code which previously saved a copy in a new workbook to a specific folder/path. (Additionally it copies and clears some figures, but this is working as it should.)
However I would like the copy to be saved at the same location as the original workbook, regardless of the path the original workbook is saved.
I.e if I need to move the workbook containing the code to a new folder/location, when using the macro, the new copy should be stored in the same folder/location as the original one.
For now it is only saving the copy into "My Documents"
Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 16-02-2009 by ceng
'
Sheets("Bunker ROB").Select
Sheets("Bunker ROB").Copy
ActiveWorkbook.SaveAs Filename:= _
ActiveWorkbook.Path & Range("D3"), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
[Code] ........
I've been having trouble recently with my workbook file size growing rapidly due to copy and paste commands and such. I found a very nice macro to reduce file size called ExcelDiet located at [url]
Option Explicit
Sub ExcelDiet()
Dim j As Long
Dim k As Long
Dim LastRow As Long
Dim LastCol As Long
Dim ColFormula As Range
Dim RowFormula As Range
Dim ColValue As Range
Dim RowValue As Range
Dim Shp As Shape
Dim ws As Worksheet
Application. ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
This code works fine until I share the workbook. Can anyone take a look at this code and provide some information as to why this might be happening?
I have one workbook for daily records of multiple data items.
These data are summed according to each item and exported to a specified cells in a monthly report.
Is there an alternative easier method to import data other than using "paste special" property.
I need to create an statement for my clients. Book A has all of the account balances. Still in book A, the account number is in column A, and balance is in column H. I just need to import from Book A into Book B, based on account number. Now on Book B, the account# is in column A and the balance needs to go to column B.
View 3 Replies View RelatedDue to some cutbacks the company I work for has taken away the database system we used to use to record debts owed to us, so I'm building one in excel (no access or sql otherwise I'd use those). We have all the debt in seperate workbooks by financial period and I'm creating a new workbook that will upon entering of a specific financial period will pull information from the pertaining workbook and report on the information.
I have a Formula
=INDIRECT.EXT(CONCATENATE("'C:Documents and Settingshughel13My Documents[",Reports!$C$11,".xls]Sheet1'!R2:R138"))
Among others which pull other ranges
Which as I understand should work just fine on a closed workbook, however it only works when the target workbook is open.
When the workbook is closed the formula only pulls the first value in the range.
On a related note the above formula and it's counterparts are autofilled down the page from 2 to 1000 as some of the workbooks have 1000 records to pull, however the ones that dont have that many, cause errors when the data is pulled, is there a way to make it so that if the cell it's pulling from is empty it doesn't pull from it or pulls a value of 0?
I recorded a macro in a workbook called masterschedule. The macro works only if I run it when I am using the masterschedule. Each week I open the masterschedule workbook and name it the current week, for example 3-26-07 schedule is the name of the most current schedule. I then open 7 other spreadsheets and paste information from the current schedule 3-26-07 (this week) onto 3 different sheets in all of the other spreadsheets. It works fine if I am using the masterschedule. Others have access to this workbook once it is named something else. I do not want to allow others to have access to the master workbook. Can someone help me with this? I've attached part of the macro below. I need it to work in whatever the masterschedule is renamed to.
Windows("MASTERschedule.xls").Activate
I am looking to create a macro button which will reset the sheet to its original state.
I have locked the cells users should not imput data into, and unlocked where they add their data.
At the end of every school year, they will need to be able to reset ALL the workbooks back to their original state with all the reference formulas.
I am thinking I will need to tell the macro to create a hidden copy of the workbooks and then upon hitting reset it will use the backup to override the current. But they will need to be able to reset at the end of every year.
I would like to have one reset button that resets ALL the workbooks at once, but if not, I could put a reset button on each workbook.
As I have just started my VBA training, I have a code that will create a backup and hide it, but I have no idea how to do the reset portion. And again, since they need to be able to reset it each year for x amount of times, I don't know how to get it to keep having a fresh backup and get everything to its original.
Once I open a new workbook, how do I make it the active workbook to have a function make changes to it? Problem I am having is even after it is open, the function will only make changes to the workbook containing the Macro (Thisworkbook)....
View 9 Replies View RelatedI have placed a workbook on a network drive. This workbook contains sensitive information and I would like to protect it to a "feasible" degree. I have written the following code
Private Sub Workbook_Open()
Dim userid As String, valid_userid As String
Dim i As Integer
valid_userid = "MISI01/"
userid = GetUserName()
userid = UCase(userid) & "/"
i = InStr(1, userid, valid_userid)
If i = 0 Then
MsgBox ("You are not authorised to use this workbook")
ActiveWorkbook.Close False
Exit Sub
End If
'create menu when workbook opens
Call Module1.CreateMenu("")
Call Modul1.check_filedate("")
' This will disable the user from moving, renaming etc the sheets
ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub
As can be seen from the above code, I check if the current userid is one of those that are valid to open the workbook. If not the idea is to tell them and close the workbook immediately (this bit isn't tested yet). This code is not supposed to be hacker proof, but to prohibit the casual user from reviewing the the data.
I don't believe this is possible but I've been wrong before.. Is it at all possible to Kill the current workbook using VBA? I " saved as" in another location and do not wish to keep the current workbook.
View 3 Replies View RelatedA simple one for you, but again my Google skills seem lacking. This must be common practice but until one knows the key combination...
I have created a summary sheet with lots of formulas which analyse other sheets in a workbook. I want to copy this summary sheet to a seperate workbook to use as a template.
Of course, the copying process always links the new (template) sheet to the original workbook it was copied from. How does one copy without forging this link? All I want are the formulas copied across totally unchanged; I really *don't* want to manually edit 50 or so formulas!
I would like to create a copy of an open workbook.
Workbook.SaveAs would seem to be a possibility, but this closes the original workbook - I need to keep the original workbook open as well as the workbook it has been "SavedAs".
I do not want to close the first workbook and repoen it as there are instances where it may be protected and I do not want the user to have to reinput any passwords etc.
I have some code written to duplicate a template and rename the copy to "Working Copy".
But if I run the code more than once, it breaks as VBA tries to overwrite the sheet with the same name.
Would I would like is for the macro to check to see it already exists and duplicate the copy with some type of incremental integer. Thus the first duplicate would be Working Copy 1, and if the macro is run again, the duplicated copy would be Working Copy 2, etc.
here is what I have so far:
Sub SCButton()
Dim i As Integer 'for making Working Copy 1, 2, etc.
Sheets("SCTemplate").Select 'this file will eventually be hidden and thus the user with only see the wokring copies.
Sheets("SCTemplate").Copy After:=Sheets(4)
Sheets("SCTemplate (2)").Select
'some sort of If statement here to check for the sheets
Sheets("SCTemplate (2)").Name = "Calculation"
I'm trying to save a copy an excel file in the same directory of the original file. The code is pretty standard but it does not work if the file is stored in C: (only c: ) Is that a bug or am I doing something wrong? Anyways here is the code in case someone is interested in trying it out:
Sub CreateCopy()
ChDrive ThisWorkbook.Path
ChDir ThisWorkbook.Path
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls", _
InitialFileName:="CMS_" & Format(Now(), "mm-dd-yyyy"))
If fileSaveName <> False Then
MsgBox "Backup copy saved as: " & fileSaveName
End If
End Sub
I want to have a user press a button on a worksheet page to save a copy of the workbook (and be able to name it and choose where to save it to) without closing or modifying the original. Note that if you "save as" manually it renames the workbook and closes the original.
View 7 Replies View RelatedAt my work we are using Excel as a CMS to produce SQL scripts for insertion into a database. The data is then turned into mobile web pages. This involves multiple workbooks - 1 for each main page with the sub-pages as extra worksheets in the book.
Much of the data is duplicated so we have created master workbooks then copied them and laboriously referenced the relevant cells back to the original. That way if any data in the master is changed, all copies will change simultaneously.
This is obviously not the most efficient way of publishing web pages but we are stuck with it for now. So I am wondering if it is possible to write a VBA code to create a copy of the master and then reference all required tables in the worksheets automatically? The tables are all named ranges.
I have a workbook within which i have a worksheet that contains a lots of macro code (coded by me). As the workbook gets used by various people, i need to copy the worksheet and the macros across to the updated workbook, which doesnt contain the macro worksheet at all.
I have tried to copy it across by clicking on its tab and using the move or copy facility. This copies the sheet across as required. But for some reason, the macros all reference the old workbook. A small bit here for example for some reason opens up the old workbook and then performs the code in the old worksheet:
Sub SelectAll()
For i = 12 To 20
Set curcell = Worksheets("Form Generator").Cells(i, 3)
If curcell = False Then
Cells(i, 3).Value = True
End If
Next i
End Sub
As curcell is equal to worksheets...() i would have expected it to use the local worksheet, ie the one that the macro is attached to. So why is excel proactively hunting out the old workbook and sheet? is the method i used to copy across the sheet with the macros incorrect? If so, how should i go about it?
I have a Excel workbook (wb1) where the columns are showing the months and the rows are showing different actions/activities. If an action takes place in a specified month the cell corresponding to both the action and the month is marked yellow.
In another workbook (wb2) I want to be able to retrive data from wb1. What I want is that when wb2 is opened it should do a search in wb1 to find the first action/activity that takes place in the current month. The activity/action text should be imported to a textbox or cell in wb2.
i have an excel database regarding about 1000 different schemes having the details of their initiation date, budget allocated , up to date expenditure, this month expenditure and many others too. this is a shared workbook and relevant officers enter their data in their relevant columns and rows. the file is shared. now i want that any changes done by mr. A, for eg. at record No. 09 could be intimated to Mr. B who is working at record No. 560. may be via msg box or any other source.
View 1 Replies View RelatedI am using the (borrowed) code below to select and import a worksheet into my workbook.
View 8 Replies View RelatedI have a workbook that contains over 100 worksheets with stock data and price information. It uses a screenscraper to update the workbook each day with the latest day's price, and then exports each of these .csv files into a local directory. The macro for this is working. In effect it is generating a price history file automatically in my absence for me that can be read by charting software.
What I would like though is a macro to loopthrough and backfill missing price histories on each worksheet. I have a source of .csv price histories already, but would like to avoid having to cut and paste each of the 105 files manually, as it may become a regular occurence.
Each worksheet that requires backfilling has the stock code in the cell "A2", so that can be used to search for the filename Range("A2") & ".csv"
This is as far as I have got - however it results in a runtime error (91) Object variable or With block variable not set, pointing to this line:-
VB: Workbooks.Open Filename:="D:FinancialData SheetsSpreadsheetsPension" & Ws.Range("A2").Value & ".csv"
VB:
Sub BackFillData()
Dim Ws As Worksheet
For i = 1 To 105
'Sheets.Add
ActiveSheet.Name = "Fund" & i
[Code] ......
I am troubleshooting my macro that seems to cause a data shift with the Letter "F" when I import multiple CSV files into one spreadsheet. When I go outside of the macro and record a macro and import the CSV into a workbook it works perfectly fine. But there must be something in this code that is causing the shift:
VB:
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Public Function ChDirNet(szPath As String) As Boolean
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
[Code] ....
I have attached an excel workbook that shows how the macro imports vs. a regular import. Why "F" is causing a shift in the data.
Data.xlsx
Need code to import a worksheet (there is only one) from another workbook. But I just get gibberish when I import it. Both files are .xlsx files.
Here is my code below. I used this to import a CSV file. I don't know if I can't use this to import excel files, but it just doesn't seem to work for me.
Code:
Sub load_ClientCustomerData()
dim fStr as string
With Application.FileDialog(msoFileDialogFilePicker)
[Code].....
I found this code on the net, and it works, it DOES import the sheet specified from a closed status, but it adds 0 where there were blank cells.
The worksheets are static, the names will not change, but the information on them varies in # rows but the # of columns depends on the page..
The use of this import is to upgrade the program, by importing the sheets from the old version.
The first part browses for the file, which is good, because it could be stored anywhere and named anything.. I have it set up to "Click to Import"....
Private Sub CommandButton1_Click()
GetValuesFromAClosedWorkbook "C:", "Book1.xls", _
"Personnel", "A:H"
End Sub
This next part does the importing, but it fills all the columns and rows that were blank with 0's and fills all the way down to 65536 thru my column H that I specified that had data above.
Sub GetValuesFromAClosedWorkbook(fPath As String, _
FName As String, sName, cellRange As String)
how this code can be modifed to do the following..
Detect and copy ONLY the used range of the closed workbook.
I have a workbook called summary with a sheet called "detail" I have 4 workbooks Called week 1, week 2, week 3 and week 4. All the week workbooks have a sheet called Summary. I need to import the summary sheets from each week workbook into the the detail sheet. All workbooks are in the same folder. I need to only copy columns A,D,F,G.
If someone can post code or point me towards a thread I can figure what changes need to be made. I am getting better but slowly!
I am using excel 2003. I have more than 500 text file which are result of some numerical analysis. I have another bunches of the same file number. I can record and play around with macro.
What I want to do is
-copy selected cells from imported text file in excel and paste in new or in first opened excel file. I have recorded macro for one file including importing from text to excell, copy and paste the selected file (look the macro below). But I have more than 500 files and I want to do the macro the same thing for each file in one excell file. Do i have to make one macro for each file??, that takes much more time than manual import and copy paste. The cell position and range to copy is the same, but have to be pasted in new row (in one excel summery file). The file name of each text file is different and all are in the same folder.
Sub text_to_excel()
Workbooks.OpenText Filename:= _
"I:ResearchVALERI_germinationvaleri_slopevaleri_slope_COREL_DHPoutputDSCN2589.txt" _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _ ..................
Copy data from workbook, open existing workbook, select range and paste. But my copied data is lost.
Sub Select_Copy_Paste()
'
'
Windows("ElektroFunctiesDatabase.xlsm").Activate
Sheets("PowerSupply's").Select
Range("A2:I6").Select
Selection.Copy
[Code] .........
' Here i need to do something to paste data into r.address?