The other day, I had posted a macro, which copies one of the sheets from a workbook, on to a blank sheet, and then saves it with a name obtained from the value of cell E7. I required certain modifications to it, before I could implement it.
You will find it here and the code looks like this:
I am using excel 2010 on windows 8. I load a file which has been sent to me via e-mail and save it with an upgraded name, I then do a large amount of data input and save the file again, I do this regularly and even test load it at times to check it has saved, finally I save and close the file and then on reloading I discover the version I have is the first one I saved! all the work I have done on it has disappeared! I also sometimes find random .tmp files with non-sensical names in the folder to which I have saved, but I do not have the rights to open these .tmp files.
View 2 Replies View RelatedI'm creating a template xlsm file that has built in marcos. This file will be used by the Engineer as a template, where they fill in the data, and then send it to the team. when the case in finished, they will use clear data macro.
The Problem I want the template to have a macro runs with startup of the file that checks for the lastest template in a network folder (shared folder where they will have read only access). If the used file is the latest one, OK. If not, give a custom msg with the location on the network.
I have a macro used to save a worksheet, its placed on the DATA sheet.
is there a way so that when the user clicks this button each row 12:200 is checked for empty cells in that row (cells B:P). If a row is found the ref number in column A of each row is placed into next available row in Sheet3
For example :
A12 = 12345
A13 = 6789
A14 = 9876
A15 = 54321
the DATA sheet is populated with various data in rows 12:15
it is found that B12, L12, E14,F14,P15 all have no data, so the values of A12,A14 and A15 are placed into next row in Sheet3
Result in sheet3
A2= 12345 (valuse of A12 in DATA Sheet)
A3= 9876 (value of A14 in DATA Sheet)
A4= 54321 (value of A15 in DATA sheet)
Finally a message box to state there is missing data, for example
"there is missing data from refs 12345 , 9876 , 54321" ie the list pasted into sheet
I have a spreadsheet that imports data, manipulates it then deletes 2 of the sheets then saves the file under a different name to the network. Is there any way to save this new worksheet without it storing the macros - so when the user open it, only the data is there and they get no prompt to enable macros?
View 6 Replies View RelatedI am setting up a "save as" macro that saves a file by replacing another file in a folder. Even though the macro has been recorded by approving the replacement (the prompt appears "the file --- already exists. Do you want to replace the existing file?"), when I run the macro, I am again prompted about replacing the file. Is it possible to avoid the prompt so the file is automatically saved by replacing the named file?
View 9 Replies View RelatedI am trying to save my workbook in a specific directory with a specific filename and to incorporate a date field from within the spreadsheet. I have changed the format of the cell so it does not include / as i know this would not work. Saving it is the easy part but adding in the field from the spreadsheet is where im falling over.
View 2 Replies View RelatedHow do I save the spreadsheet with the current name of the spreadsheet in a macro?
Example:
I want to save the spreadsheet to a network drive using the current name of the spreadsheet (it changes every other day). But I dont know to put the name in the SAVE statement. Here is what I have now:
I have a file that I save with a new version number each time I make major changes. The file name currently is: "Telephony Equipment Inventory v26 (Summary).xlsm". The "26" is the variable number. give me the vba code to ensure I open the file with the highest version number?
View 6 Replies View RelatedI'm using this bit of code to save a cvs file as an xls file.
Code:
'Save file
Dim Filename As String
Filename = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If Filename "False" Then
ActiveWorkbook.SaveAs Filename
End If
However, I would like the file to be named Win7Sync-
What i want is for my user to click the save button linked to a macro that saves my file as Order0000. If there is a file already called this i want it to change the name to Order00001 and so on for Order00002.
Is this possible and if so what VBA code
I have a excel 2003 template that loads a csv file and create a graph.What I am trying to do is remove the Workbook_open macro after it has been run so when they save the file it will not save the macro in the new file.
View 3 Replies View Relatedhow to get version number of the .dll file (in the path C:program files....) using VBA code. The properties of that .dll file has a tab named "Version". so i need the version number displayed on that tab.
View 3 Replies View Relatedhow I could amend the following code so that my target worksheet includes file versions? The worksheet range "Folder" contains a string specifying the target folder and the worksheet range "IncludeSubFolders" is either TRUE or FALSE. I can trap FilePath, FileName, FileSize and DateModified fine but not File Version (I'm working with DLL libraries).
Code:
Dim iRow As Long
Sub ListFiles()
iRow = Range("DatabaseStart").Offset(1, 0).Row
Call ListMyFiles(Range("Folder"), Range("IncludeSubFolders"))
End Sub
[Code]......
I have a file that is on a server. When I open it on my excel it opens an old version of it and not the newest copy. If I open it on a coworkers computer, it does open the correct newer version. How do I change this?
View 4 Replies View RelatedI have a form in Access with a button that opens an Excel file. I think I've seen vba code to tell if a specific Excel file is open already. What I'm looking for is a way to tell if an Excel file is already open when you don't know the filename of the Excel file. Is that possible? Or at the very least is there a way to be able to tell if Excel is already open?
View 4 Replies View RelatedI have a spreadsheet with a macro that saves a backup s/s to a particular folder, but the problem I have need the macro to check the filepath is that of the original s/s before running.
Its so that when people take copies of the main s/s and save them to their desktop I dont want the copies being saved in the backup folder
macro is below
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveWorkbook.ReadOnly Then Exit Sub
If MsgBox("Do You Want To Save A Backup?", vbYesNo) = vbNo Then Exit Sub
Dim fso As Object
Dim objFiles As Object
Dim myWkBk As String
Dim myFName As String
Dim BkUpDir As String
Dim CountFiles As Integer
Dim mydate As Double
BkUpDir = "k:singstatappealsappeals trackerBackups"
Set fso = CreateObject("Scripting.FileSystemObject")
Set objFiles = fso.GetFolder(BkUpDir).Files
On Error Resume Next
'the actual counting of files takes place here.
If Err.Number 0 Then
CountFiles = 0
Else
CountFiles = objFiles.Count
End If
If CountFiles > 4 Then
MsgBox "You have " & CountFiles & " saved workbooks. Perhaps you should delete some?"
End If
mydate = Format(Now(), "ddmmyy")
myWkBk = ActiveWorkbook.Name
myFName = ActiveWorkbook.FullName
fso.CopyFile myFName, BkUpDir & mydate & myWkBk
End Sub
How can I make this code repeat itself on different lines?
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheets("Sheet1"). Range("A10").Value > "" And Sheets("Sheet1").Range("B10").Value = "" Then
MsgBox "Please Enter Order Number in Line 10"
Cancel = True 'cancels the save event
End If
End Sub
The code works perfectly for that one line but I need it to repeat independantly in the same columns but for rows 11 up to and including 23.
If I need to make the message generic like " Please Enter Order Number" then that's fine.
I have numerous spreadsheets that I need to open and unhide a sheet, that has XML data stored in cell A1. What I need to do is copy that data in cell A1 and paste it into a text document and save that as an XML file saved as the XLS workbook name with a date stamp.
I'm running into many issues, the main issue is the saving as current file name, and the formatting of the text/XML file.
Here is my current code, which doesn't reference the current file name and is just very generic. Once I get the saving as file-name correct and the formatting of the xml file correct, I will work on it a bit more.
Code:
Sub Test()
Dim Rng As Range
Dim wb As Workbook
Set Rng = Range("A1:A2")
Set wb = Workbooks.Add
With wb
Rng.Copy
[code]....
I was wondering if there is a way to write a macro that will take an excel file that I have called Alldata and then have it copy the first two sheets to a new workbook named something else( FullSparameter_0) and then copy the name of the third and fourth sheet into the new FullSparameter_0 workbook and then copy column A-E from sheet dB Alldata.xls and paste it into the FullSparameter_0.xls workbook in the same dB sheet in the same spot. And do the same thing with the Phase Sheet. i know this sounds confusing, but im really new to the VBA stuff.
I just need 15 different files from the original Alldata.xls that will be named FullSparameter_0, FullSparameter_1, .., FullSparameter_14. but each file will have the same two first sheets, Setup Information and Cal Verification, and the same last two sheet names, dB and Phase, but then in the dB and phase sheets, it will copy over the next four columns of data. so in FullSparameter_0 it will have column A-E from sheet dB in Alldata.xls in the dB sheet in FullSparameter_0 (Same with the Phase Sheets). Then in FullSparameter_1 it will have column A and Columns F,G,H and I (for dB and Phase). In fullSparameter_2 it will have column A and then J,K,L and M. Each FullSparameter_X will have the exact same first two sheets and then the other columns for the last two sheets will be pasted into columns A-E in dB and Phase.
found the following code which works for 1 workbook at a time. I am trying to save 7 workbooks at 1 time. Is it possible?
Sub SaveAsCell()
Dim strName As String
On Error Goto InvalidName
strName = Sheet1. Range("V77")
ActiveWorkbook.SaveAs strName
It will work on the first sheet but none after that. I need to have each workbook saved with the value in cell V77. Also if that is possible, is it possible to change where the file is saved as well?
Right now I have a master workbook that will open the 7 other workbooks, paste data onto several pages in each workbook. I would like the macro to save the workbooks. The workbooks are named: 02 Tuesday, 03 Wednesday, 04 Thursday, 05 Friday, 06 Saturday, 07 Sunday, 08 Monday.
This is probably a simple question, but I couldn't find the answer by searching...
I have a macro that I wish to run which calls one of two different macro's depending on if the user has Excel version 2007 or if they have an earlier version.
This is to overcome 'Save as PDF', which only works in Excel 2007.
I need to find out which version of excel i am using in macro.I mean whether it is 2003 or 2007.Because i need to put 2 diff codes according to the version of excel.Can any one please provide me the macro code to findout the version of Excel.
Looking forward VBA coding for:
If input is entered into column C (range C2:C100), then row cells for columns D, E ,F, H or I must NOT be blank upon save.
Could be that one or more of these row cells are left blank by mistake.
An error msg pops up upon attempting to save, stops the save and colors each cell yellow that needs info entered into.
Using Excel 2010.
I have a spreadsheet file (excel 2003). I'd like convert it in CSV with delim.
But when I save it appear message that say "the file contains some things not compatible with CSV with delimiter" then I can save it and lose information. After when I open it with Word it looks ok but delimiter is ";"
I set delimiter in windows and inside excel too to ","
But I can't undertand what are things not compatible. there are only words and number as general format....(there aren't "," and each other strange character).
How can I modify this code to simply display the pdf and not have it save to anywhere.....
Sub SavePDF_Click()
Dim UserSite As String
UserSite = Sheets("Site").Range("D102").Value
Sheets("View your Graphs").Select
Sheets("View your Graphs").Activate
[Code]...
Saving WORKSHEET file. I'm using this code:
View 4 Replies View RelatedI am trying to create a .csv file from an Excel file using VBA and when I use the ActiveWorkbook.SaveAs command, only the first row of text contains " quotes around the text. I have multiple rows that must contain the quotes. I have tried using """", and Chr(34), but then that puts """ (3 quotes) around my text. Is there a different function that I should be attempting to use? I am using concatanation to create a field where I would like the quoted text and am using the ActiveCell.Value command (ActiveCell.Text didn't seem to make any difference).
View 9 Replies View RelatedI have the below code to save a file once cretaed from master data, but it keeps failing on me.
View 3 Replies View RelatedI have a code that performs a task for me and will save the file at the end. It currently saves as a name I have selected and today's date. The problem I now face is that I will be completing this task several times a day and it will not work as the file names will be the same. Is there any way to save a unique name each time?
My code to save below:
Code:
ActiveWorkbook.SaveAs Filename:= _ "T:Lender UpdatesLender UpdatesLloydsCompleteAcknowledged " & Format(Date, "dd.mm.yyyy") & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False