I have a VBA form that the user enters information into and once they click submit on the form the code opens a new workbook and then enters the information from the form into that workbook. What I need is for that code to then save the workbook with a filename from one of the fields entered into the form but I'm not too sure how to do this.
View 3 RepliesI need an vba code that if my workbook is open say at 14:00 that it automatically save and close the workbook.
View 2 Replies View RelatedI've found afew examples off google searches, but my attempts to edit them arent working...
i want a macro to copy a range, say a1:e5, and paste the values and fonts/boarders/etc (ie no formulas) into a new workbook
the ones i found copy a whole sheet, i just want a portion....
I have a csv .txt file that I am importing into Excel, and then creating a pivot-table from that data. This results in 2 tabs within my workbook, pivot-table and data. Using the following code does everything I want it to, but when I re-open the saved .xls file I only have the pivot-table sheet. I'm specifying to save the full workbook, aren't I?!?
Sub format_bgas_flowtracer()
Dim v_excel As Excel.Application
Dim v_worksheet As Worksheet
Set v_excel = CreateObject("Excel.Application")
On Error Resume Next
Kill "c:oh_for_gods_sake_JUST_WORK_YOU_STUPID_SODDING_THING.xls"
On Error Goto 0
v_excel.Workbooks.OpenText FileName:="D:gas_flowtracker_out.txt", Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _ ...............
I would like a macro that saves the current open workbook to a directory specified in a cell on the open work sheet.
View 4 Replies View RelatedI am trying to create a macro so that when i save an entry the form sheet looks in the tavela master sheet to see if there is a match on the record number (in the A column) and saves there as an update if it matches and then if there is no match, saves it in the last empty row. this is a column to row copy so it has to be paste special to transpose. also i have a password on the sheet which i use to unprotect then reprotect. i have successfully done these two things separately but cannot combine them! i know not very much about visual basic code just what i have read here and so i am trying to make this code work.
Code:....
I'm working on a project where I need to populate a column with vlookups taken from different workbooks, I have for the most part got it working. However currently, everytime it populates a cell it prompts the user to select the sheet from the workbook as there are two. The sheet name is always the same so I tried to add it into the code so it would avoid having to repeatedly click okay while it populated the table.
This is the original code where it asks for the user to select the sheet every time.
[Code].....
This is what is produced when it is run and the user selects the worksheet each time (which works perfectly fine):
[Code] ...........
I tried to enter the Full Costs sheet name into the code like so:
[Code] ......
However this produces the following:
[Code] .....
The issue I have found is that the square brackets that are around the filename are generated automatically, they aren’t in the actual filename and I haven’t put them in, so I am struggling to work out how to add the sheet name in where it wont be included within the square brackets, as that is what's breaking the lookup.
I'm working on a spreadsheet that will access two weeks worth of data at a time. Is it possible to have the filename reference contain a variable or a link to another cell in it?
For example: ...
I have a filname called ex: "Statusreport xxyyzz.xls"
I'm only interested to put xxyyzz in a variable and reuse it for saving another workbook.
How can I use the different string functions for this. The word "Statusreport" is used for apporx 20 different workbooks.
Have a file lets say is named
sample.xls
Does anyone know how to retrieve the name of the file in VB?
just the name and put it in a string?
Private Sub cmdImport_Click()
Dim exportFile As String
exportFile = "c:jpmimportTrinity_ImpImport_" & Format( Date, "dd-mm-yy") + "_" + Format(Time, "hh:mm:ss") & ".csv"
Open exportFile For Output As #1
I put a watch on exportFile and it's correctly set to: "c:jpmimportTrinity_ImpImport_15-02-07_17:55:01.csv"
However if I try to run this I get "bad file name or numer" when I open for output - the path exists and I can write to it, so must have a fundamentally wrong approach.
referencing a variable during a data import. I have searched the forums here and the web for a couple weeks and attempted enclosing the variable in many different symbols.
Nothing I have tried works. I have also read chip pearsons guide but I was still not able to get it to work.
I am trying to provide a way for the user to input the filename and location in an input box and then use that variable to import the data. It is only one file that is needed.
The message box filename is only included to allow me to verify the text input.
Dim Filename As String
Sheets.Add. Name = "All Data"
Filename = InputBox("Enter Filename: ", "Enter Filename Location")
MsgBox Filename
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Filename", Destination:=Range("A1"))
.Name = "SHOAlarmsJune2-9"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
I am trying to get a module wide variable to provide the current filename to the various subs.
View 2 Replies View RelatedI want to have a macro (because i do so often) that for any active workbook that I'm using will....
capture the full pathname and file name and place into clipboard for pasting elsewhere.
eg run macro and the following is added to clipboard.....C:Test DirectoryTest_File.xls
I open two spreadsheets only. In one of them I would like the filename of the other spreadsheet shown in cell A1.
The reason why is the filename is a 6 number date and I'd like to confirm it showing in full format.
I am trying to ensure that m work book keeps the same name even when I email it to others.
I have been told on this forum that it is possible for a workbook to check its own name once opened?
I have two different names that this file can be called 1st is the one that is in use all the time and the 2nd is a archive version where the name changes each month.
The file name I wish to keep the same for the no archived version is Toolbox and a predetermined number I have a workings sheet called workings and in a cell i have a formula =TEXT("Toolbox Application " & Cover!E10,) this will give me the file name that i need to check for example it could be Toolbox Application 00089 or Toolbox Application 00967.
I also have a cmd that archives the workbook and saves it as Toolbox and the Month it was for Toolbox for Jan for example
I also need this to open if it is an archive
if neither of the names match i need the workbook to close and a msgbox to tell user to rename the workbook correctly.
I have a file that I want to name based on content in certain cells. I.e. "Q-(text from cell G1)-(text from cell B7)-Date(mm_dd_yy)". I want to be able to specify the location where I save this file each time I try to save it, but I want it to update the file's name if any of the parameters have changed.
I have some code generated that does just about everything right except that it automatically saves a version of the file in the folder where the original is located even though it also brings up the dialog box and allows me to save it in another location.
Basically, I want to fix the code below so that it doesn't save what appears to be a backup file each time. Should I be using some sort of savecopyas?
Code:
Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Add the quote # to the header
ActiveSheet.PageSetup.RightHeader = "Quote No. " & Range("G1").Value
[Code].....
I have to many refrence links I need a macro that replace the filename. How to replace workbook refrence filename on a formula using cell value,
example:
from [13.xlm]2014.Goals'!C8 to ["cell-value".xlm]2014.Goals'!C8
I am building a macro and am just not figuring out how to do this. I am trying to create a new workbook, then give it a name. Simple right? Well, I have the format for the file name to be "mm-dd-yy Transmittal #.xlsx" but I can't figure out how to make the # part of that equation start as 1 but if the file already exists change it to 2, if that exists, change it to 3, and so on. Here's what I have so far:
Code:
Sub Transmittal()
Dim NewBook As Workbook
Dim MyPath As String
[Code]....
I don't think I'm allowed to do "x + 1" so that is probably where my problem is coming from.
I have a workbook that gets saved many times with many different filenames.
All I want to do is have cell A1 display as text the name of the file.
eg similar to the date function, there should be a = filename function .
Im trying to create a macro that when i click the button a new workbook is created and saved as the value from C5 cell.
View 9 Replies View RelatedIs there a way to find the full name and path of the workbook the macro is running in?
View 4 Replies View RelatedI'm trying to record a macro that calls up the saveas dialogue and uses a filename from a cell (it's the w/c date). I can start recording the macro, open the dialogue box and copy the contents of the cell but i can't get it to paste as the filname.
What I'm trying to achieve is to allow users of a muli-sheet workbook to be able to click on a 'save' button on any sheet and have the workbook save with a filename which includes the current week commencing date. This way all the weeks changes will be in just one file and from the following monday a new file will be started the the old one left alone as an archive.
Am working on a procedure to open all files in a folder and copy some data from the files into another workbook (master). I have this piece of the code. However, I also need to get the filename (from which data was copied) and paste this into an adjacent cell in the master (so I know which file each data point came from).
View 8 Replies View RelatedI have the following codes in which I am to refer a file and folder to do some further processes.
myfilename = "C:Documents and settingsacsMy DocumentsEntrymyfile1.xls"
folderPath = "C:Documents and settingsacsMy DocumentsEntry"
I want to enter these two paths in a sheet in my excel report file (For example, I am running the report from Report1.xls, in which there is a sheet name "Filepaths". In this,
in B5, I would like to enter the Filepath (B5 named as "FILEPATH")
in B6, I would like to enter the Folderpath (B6 named as "FOLDERPATH")
So that, if I am copying the folder to another area, I no need to change the code every time in the VBA editor. I can do changes in these cells and it will be taken as the path to run the code further.
I need to enter a revision string into a file.
Basically, I need to combine the filename and its generation time and then encode it into a string (i.e. a 6 digits HEX code or 6 digits using [0-9][a-z][A-Z]) so it would generate a pretty much unique revision number.
So, is there any function that you can think of that would do something similar?
If not, what is the best to tackle this?
Generate a CRC or md5sum?
What would be in your opinion a quick and efficient way to solve this tricky one?
I have a spreadsheet that from a button I want to run a macro that will input todays date, the value in cell A1 as the filename into a default dialog box that is at a default file path. I have been trying to do this for several hours and can not completely get it done.
View 9 Replies View RelatedI need my program to:
- find the cell containing the string "Datum/Tid"
- record the column and the row of the found cell in two variables lCol and lRow
Here is my
Sub test()
Dim rFoundCell As Range
Dim lRow As Long
Dim lCol As Long
'Find method of VBA
Set rFoundCell = Range("A1")
Set rFoundCell = Worksheets("Sheet1").Range("A1:Z50").Find(What:="Datum/Tid", After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
'for anyof the two lines down I get the message "object variable OR block variable not set"
lRow = rFoundCell.Row
lCol = rFoundCell.Column
End Sub
Replace filename with variable in "Activecell.FormulaR1C1=" expression
I have recorded a macro which contains several lines of code like this one:
I have created a macro to split out information from one excel workbook into 2 additional workbooks, and format them the way we need. Everything works fine on the filename I originally recorded the macro from, but is giving me a 'subscript out of range' error when trying to run the macro on a file with a different name.
currently, my test file has the 2 following fields that I somehow need to have as variables:
Windows("insmacrotest20090418.xls").Activate
and
Windows("Book1").Activate
I need the filenames and workbooks in the parenthesis to be variable so that the macro can be run on other reports with varying filenames.