I wrote this code to open an excel file when a button is pushed. I also have another piece of code to close the excel field when another button is pushed. The problem: the code to close the excel only works on my computer. There is no error message, but the file doesn't not close on others' computer. Can you please advise on a solution?
View 7 RepliesI have created several excel files as I cannot open them all at once since they are too large. So I have to open one excel file whilst closing another.
part1
part2
part3
part4
part5
I want a macro that opens the excel file part 1 and part 2. Then it should close part 1 and after closing part 1 it should open part 3. Once part 3 is open, part 2 should be closed and when part 2 is closed part 4 should open. When part 4 is open, then part 3 should be closed and part 5 should open.
I call it from a macro. It is supposed to write some temporary files, and close them in the end, and delete them. Sometimes, the dll can't close properly, and the text files remain open.
With error treatment in my macro, I can continue execution, but i need to delete that files.
Since the dll DOES NOT CLOSE the files, i'm not able to delete them in my macro.
I need some way to close that files, in order to delete them.
When I use GetFile to reference the file, it remains as "File", which does not accept "Close" method. I can reference the file with myfile.OpenAsTextStream, but this way it seems to me that the file is opened again, and the "Close" method does not work either, with the file defined as a TextStream object.
If I close excel, I'm able to delete those files, but that's not the way I want. Since IT'S OPEN IN EXCEL PROCESS, I imagine there's some way to close it.
I need to open about 2000 documents one at a time, and save them with a different name.
they are a mix of word documents, PDFs and .jpg pictures.
some VBA like:
Open.file strPath & nextfile
where strpath = C:Temp
and nextfile = picture01.jpg
What should the line be? or is it more complex than a single line ?
I am trying to open an Excel file that was emailed to me in zipped file that requires macros. When it asks me if I want to enable macros it gives me an error and will not open. Is there a setting I need to change?
View 5 Replies View RelatedI am using Excel 2003.
It’s been working perfectly up until around an hour ago. However, now when I close Excel, I get compile errors.
These compile errors seem to be because Excel is trying to access controls in the workbook after it has closed. Since the workbook is closed, VBA can no longer ‘see’ the controls, and therefore it thows up errors.
I’m also getting a similar error on a Worksheets("DataExplorer").unprotect line, which seems to be because the worksheet isn’t there after closing.
These errors only occur when I close the whole excel application using the big cross in the top right. If I just close the workbook (using the smaller cross just below the big cross in the top right of excel), it doesn’t throw up these errors.
Just to clarify: all of the code runs perfectly when Excel is open. The errors are being thrown up for lines of code which run without problem until Excel is closed.
When i open excel it give an error and it close. My computer up to date about office upgrades. And i try to repair my office but it doesnt solve my problem.
View 7 Replies View RelatedBelow is the code to open a csv file and paste it in the workbook "ResultFile". When i click on a button, a csv file will open to copy the values and paste it in "Resultfile". Now, i want to close the csv or excel file to be closed once copying has done.
where to put the close file.
[Code] ....
close the inactive file
I'm using the
I have a template where users provide input in specified fields. Upon completion, pressing a macro button emails the file to administrator.
find out a code that once email is sent, perform following actions simultaneously;
Pop up message to appear as “Message Sent – Do you want to process another request?
If reply is Yes, the sheet is refreshed
If reply is No, File is closed without Saving
I have this code that I've sourced from the internet, i have used to disable red "X" on the top right,
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = Not bClose
If Cancel = True Then MsgBox "Please Save your data and select File - Close from the main menu"
End Sub
but when I select File and then close .... it runs the same macro? eh? i want x's disabled not the file close ....
The code below works perfectly when run from a macro button on a worksheet or when initiated using the VBA run option in the VBA editor but it does not run when run from a command button on a userform. The code is
Sub closer()
Dim bCancel As Boolean
Do Until bCancel = True
bCancel = Application.Dialogs(xlDialogSaveAs).Show
If bCancel = False Then MsgBox "Sorry, cannot Cancel", vbCritical
Loop
ActiveWorkbook.Close SaveChanges:=False
End Sub
When I run the code from a command button on my userform the error message I get is: 'Run-Time error 1004 Document not saved' and the line of code that it continually gets stuck on is: bCancel = Application.Dialogs(xlDialogSaveAs).Show
i would like my sheet to save and close if left inactive after 10 mins, code would be nice
View 2 Replies View RelatedI am trying to fix is that I have two computers networked together both with multiple users all with access to the report form that I am using which is in the shared file. If I have been working on it (I am still trying to do improvements when I have time), and I have to leave for a fire or forget to close the report, no one can access it other than with read-only.
My idea was to run some type of code that either when the screen saver comes on or after so much time with no activity, it would close and save the file with a new file name(just in case I don't want the changes that I have made in the code or something).
I am working with a bunch of files to do a dashboard. I have the main file which shows end results and what my users will see "Dashboard". I have about 10-15 files which I export weekly from MS Access. and I have the "Dashboard Data" file. In this file I have a sheet which links all the information for the files exported from MS Access. In here I have named ranges and dynamic ranges plus additional columns created to look up information in other tables. I use this file as my source data for my Dashboard File so that I can reduce the size of the actually dashboard. Dashboard Data file is about 5.5 mb and my Dashboard is 300kb. I have 12 branches that need to view the data over a radius of a hundred miles so the smal file are better.
My problem: I can get the Dashboard Data file to update without opening all the 10-15 other files. I cant however make the Dashboard file update without opening the Dashboard Data file. I have it to auto update without prompting but its still tells me that it cannot update. I was wondering if there is some VBA code I could use that would work like this:
I open the Dashboard File, Dashboard Data file opens so that Dashboard file updates and then close Dashboard Data File so that the user never has to see the Dashboard Data file. I dont want them to see it or have access to it.
I opened a file, by file I mean a "file" with no extension that has commas separating it.
So I recorded a macro to open up the file and this works fine.
How do I close it? I tried
wk1 = ThisWorkbook.Name 'Main Worksheet
...
Workbooks(wk1).Activate
ActiveWindow.Close
But the problem is that "wk1" is not set equal to the name, probably because the file is not an excel file, it has no extension.
At work we use a spreedsheet to control our holidays. The problem we are having is the file is stored on a network and some people are opening the file and not closeing it once done with, this is locking out others from using it.
Is there any VBA code that will close the file, if not used in say 5 mins
Upon a button click, I have a msgBox show to ask the user if they want to save the file before closing (with the option of cancelling). If the file name is something specific, I want the file to just close without saving.
I have the following code but am having the problem of the message box showing (disregarding the first if statement) and selecting No produces the same result as selecting the Cancel button.
Application.DisplayAlerts = False
myMsgBox = MsgBox("Would you like to save this file before closing?", vbYesNoCancel + vbQuestion, "Save Message")
'If filename hasn't changed, just close w/o saving.
If ActiveWorkbook.Name = "Project Foundation v2.0" Then
ActiveWorkbook.Close savechanges:=False
Else...............
I have a piece code that allows the user to pick a file from a folder on my C:. It then copies a range of data into the original workbook that the macro was run. I would like to close the other file down automatically which shouldn't be that hard but I can't work out how to do it.
View 9 Replies View RelatedI need to check if a .txt file is open. If yes, need to close it.
View 9 Replies View RelatedI am trying to inch my way forward to writing a macro that will read in an external CSV file into a sheet (my next task will be to spread the date between multiple sheets based on regions). My first attempt at code is the following
Sub read_csv()
Dim strLine As String
Dim i As Integer
Dim filename As String
Dim ans As String
filename = "C:Documents and SettingsMichaelMina dokumentMina datakällorD123905.csv"
Open filename For Input As #1
i = 1
While Not EOF(1) And i < 10
Line Input #1, sLine
ans = MsgBox(sLine, vbOK, "hello there")
i = i + 1
Wend
' Close filename
End Sub
A couple of problems arise. When I debug the code, if I remove the comment on the close filename, the debugger complains with error 13 (incompatible types). If I comment the line and try and run the code twice in a row, I get a complaint about error 55 - file already open. I'm guessing that the close filename is the correct syntax - if it isn't, what should it be??
At the time of opening the file some macros are running automatically in my file as per my requirement. If auto filter is on then it disturbs my program. Sometimes I forget to remove auto filter before closing the file and it cause a problem in my file after opening it again.
Requirement is: Is there any code to prevent the file closing if the auto filter is on and give me a warning message 'Please remove auto filter first'.
I want to hide all toolbars and disable the red X upon opening of the spreadsheet, and to restore the main toolbars upon closing. That worked perfectly. (when I used the black code -see below)
I added in the red code as i wanted to disable the red X as well to ensure that users will always close the EXCEL worksheet via a Macro button.
The Macro will save the spreadsheet and close the worksheet and the Private sub should restore the toolbars.
I am trying to write VB code that upon opening an excel spreadsheet a message box appears that the user must read, than select yes or no. If select yes than the file will open, if no the file closes or does not open. Here is what i have tried - it doesn't work.
Sub Auto_Open()
YesNo = ("Are you sure you want to open?",vbYesNo + vbExclamation,"Will")
If_vbYes Exit_Sub
If_vbNo_Close
End Sub
I have an Excel sheet that I convert to PDF --here is Example of the name after PDF creates"Morning Report_08.16.14" , When it runs it just overwrites the file automatically if one was already on that date - I would like it to prompt to overwrite & if they choose 'yes' to overwrite the file and if not they can change the name If the new one is in addition to the one that already exists and I want it to save the new file with in the same folder.
Also when the current code is run if the PDF was left opened it stops the macro - Is it possible to see if that file is open and if so close it down so the code will work or is it possible to overwrite the file when open.
[Code].....
I do have the below code to safe a file and close it:
Sub Save_Close()
'
' Save_Close Macro
'
'
ActiveWorkbook.Save
Application.Quit
End Sub
I would like to do additionally something else. I need a macro to do the above, but it should first check if cells A2:C9 do contain text. The text could be anything.
If there is text in ALL the cells, then the macro should save and close the file. If there is text missing in at least one of the cells, a message box should appear saying 'Please fill out all cells'.
How can I put a VBA script to wait for 30 seconds, then close the Excel file (w/o saving it) which its name is stored in CSV_file_name variable, then re-open it?
I have a large file that takes a long time to close.
I would like to display a message that says " SAVING - PLEASE WAIT".
But I do NOT want any interaction from the user to continue so I can not use a MSGBOX.
How can I do this?
I have an existing spreadsheet with a column of strings (actually VIN numbers). These numbers correllate to a bunch of text files, that can exist in one of three folders (UsernameDesktop1, 2, or 3) on my desktop. What I need the macro to do is:
1) get the filename from A2 (A1 is a heading row)
2) Find the appropriate text file in one of the three folders
3) Put the folder name into I2
4) Scan the text file for some strings, and copy some data that follows those strings into J2:O2 (I can handle programming this)
5) Close the text file
6) repeat above for the remainder of filenames (about 1800 files)
I want to create a VBA that will close excel if there are no other worksheets open. if there are, then close only this worksheet.
I tried these 2 codes:
Sub CloseExcel()
Application.Quit
End Sub
This one works, but it wants to close other excel project opened (i do not want this!)
Sub CloseWorksheet()
ActiveWorkbook.Close
End Sub
Works too, but it close the worksheet leaving excel opened in background. (i do not want an empty exel in background!)