I was wondering if there is a way to open up a given program (notepad in my example) ..paste data from cells copied in excel and save the notepad file to a specific location and close it out??
The file path will be given from a data cell in excel as well as the file name.
From searching the forums i've come up with the way to open notepad
using the lines
Dim RetVal As Variant
RetVal = Shell("C:WINDOWSSYSTEM32
otepad.exe")
Is It Possible To Auto Save To A File Via A Macro?
What I Want To Do Is To Auto Save Directly To A Folder On Our System Via A Macro.
The Problem Is That Firstly It Will Need To Look Up A Cell ( D8 ) To Find Out The Job Number
Then It Will Have To Find The Folder On Our System ( Its On Desktop Under Job File Folder ) Then Comes The Hard Bit It Will Need To Locate That Job Number ( Cell D8 ) To Look Up The Relevant Folder To Put It In. All The Job Files Are Labelled Up Eg.( J2663 - Parry ) Then In That Folder Is A Sub Folder Called Docs Which It Needs To Be Saved In
Im looking to open a workbook (always same name and file path) using VB but then name it as "SourceWkb" so I can do some work on it with out having to activate it.
Code:
Sub Test()
Dim SourceWkb As Workbook
Set thisWkb = ThisWorkbook
Set SourceWkb = Workbook.Open Filename:="Q:Inventory 13Fruit 11 DC DeploymentMarks Test FileOrders Keyed Test.xlsm", UpdateLinks:=0
With SourceWkb
[code]....
I'm looking for a command for a macro, haven't been able to find it thus far. Is it possible to get excel to open a .txt for example, and copy its contents into excel at an activecell?
View 9 Replies View RelatedI am wirking on a macro which opens a 'sourcefile' and then do some filtering and vlookuping to distribute the data to several files. When i run the macro from the VBA editor, everything runs fine. But if i run it from .xls file, the macro stops after opening the 'sourcefile'
Workbooks.Open Filename:=sourcefile
For i = 0 To 13
'do something...
Next
I'm using a macro to open an other excel file to get data from it. this goes by Workbooks.Open Filename:=totalopen (totalopen =Filepath & filename earlier defined) Because the opening file can have the same filename, my question; is it possible to open the latest/newest saved file? So also look at the saving date/time of opening excel file.
View 5 Replies View RelatedI'm working on a macro for work. It's a simple workbook with about 10 tabs. I would like for the user to open up the workbook but it not have the prompt foir enabling or disabling macros. Just on this one sheet, not other macros on other sheets.
I have a user form with a TextBox (TextBox1), the user enters a filename and presses CommandButton1, this then opens the file. I have set an error trap so that if the file does not exist a MsgBox prompts the user to re-enter. So far so good, however if the error trap operates any further files entered are declared as non-existent even if I know they exist. I thought this would be quite easy but once again VBA bites me back!!
here is my code
Sub CommandButton1_Click()
stPath = ThisWorkbook.Path
stFilename = "Retention" & "" & TextBox1 & ".xls"
stFullname = stPath & "" & stFilename 'completes full path of for TextBox1
On Error Goto Errorhandler: 'go to this if file does not exist
Workbooks.Open stFullname
ActiveWorkbook.RunAutoMacros xlAutoOpen
Code to save a file C:DesktopFile.xls to a different path say F:New.xls as a backup.
I want this to happen every 1 hour so that if in case my original sheet File.xls is corrupted or deleted, I have a backup data (New.xls) which was last saved.
And also File.xls need not be necessarily open.
I have a macro that takes a text file as input and produces an excel file as output. I want a macro to store the name of the text file in a variable (without its file extension).
View 1 Replies View RelatedI have a need to open a file from my companies intranet. My current method was to open said file via the method that the recorder gave me. However, I would like ot be able to open a said file without having to start open another workbook.
This is the path:
[url]
So the command is this:
Workbooks.Open [url]
Links are not actual links
So what I need to know. Is how can I open this file without opening a workbook. I haven't been able to use the VB "Open Statement" to open a file and I don't believe that I've been successfull using the Filesystem object either.
if it is possible for vba to recognize the name of the file is into.
example:
You file's name is : Track
you change for : Music
Your code automatically recognizes that and change the code.
I am using the following code to rename a file that is being pumped out by another program.
Sub CopyAndDelete()
Name "C:Program FilesPATH1234567890_3.tvr" As "C:Program FilesPATHNEWNAME.tvr"
'
End Sub
The problem I have is the the file being pumped has a varible name. The nly constant is the _#.tvr. The first 10 digits are random numbers.
I have persued the "rhymn and reason" for the file name with the vendor and have been told it can not be changed and is random. The "_#.tvr" piece comes from the number of reports I have generated for the day. So I have 5 prefined reports that run each day and can recreate the _0.tvr through _4.tvr but the first 10 are random each time.
I would like for the rename to "ignore" the first 10 digits then looking at the file name.
I have a macro to loop through the files in a folder. The macro will then open up the files and copy over to a new workbook.
As I want to rename the worksheets created in the new workbook as part of the file name. Any idea how to extract the file name out and stored in a string?
Currently it will be named after the Cell A1 in the opened file. However I would like to rename as below.
Example, the file name : 030309_Mary Sales.xls, Worksheet Name to Create : Mary Sales.
File name : 030309_John King, Worksheet Name : John King
(in the code, 030309 taken from my menu sheet,D4 is the keyword , to search in files containing D4, where D3 is the directory to look in)
Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
I am trying to edit some formulas Under the Mon-D15 worksheet in this file. The # NUM is where I am having trouble. Basically, what I want to do is only rank the names in C15:C18. I was successful in doing this with C10:C14. But, for some reason it will not work with the lower values.
Question #2
Is there a way to make Columns I:O stay static through Rows 1:24 (width=3)?
I want Rows 25:40 to be different widths (width=2) in Columns I:O.
loop and range function to apply in the below code through which I can avoid writing code for all the rows.
I am trying to open excel files located in single folder from files name (along with the path) in single worksheet (Column B and Row 1 to 500).
I have created follwing code which opens the file and then runs a macro in it.
a Sub Test()
Dim strFName As String
strFName = Sheet1.Range("B2").Value
[Code].....
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 would like to know if it is possible to run a macro from excel for another program and then have results imported into excel.
Currently I have a program called Reflection WRQ that I run a macro in and the results are captured in a text file. Then I run a macro in Excel that imports that text file. If possible I would like to have my macro in Excel run the command in Reflection and then import the results in Excel.
I'm creating a report at work that I want to be able to print out a PDF copy of a worksheet (QPRForm_V6). I have Excel 2007, and have using the code below that works for me, but when people using Excel 2000 try to run this macro, it gives them a 400 error. Everyone has this program called PDFCreator (that is set up in the printer folder).
View 2 Replies View RelatedI've only used VBA to expedite Excel processes, so this is my first exploit into using it for something else. So there's a radio program I enjoy listening to. It's not local, so I have to stream it online. I got a job recently, and I can no longer listen to the program during the day (my job doesn't allow streaming audio). So basically to hear the program, I have an internet explorer add-on called freecorder that records streaming audio. Basically what I've done till now is start recording the streaming radio before work and then stop recording when I get home. That creates quite a large file, so I'd like to set up a macro that does the following:
1) Open internet explorer at noon
2) Go the website from which the radio station streams
3) Click the button that starts freecorder recording
4) At 3 o'clock click the button that stops recording
I need to write one Macro program for below logic.
A
B
C
123
123
‘TRUE’
123
234
‘TRUE’
[code]......
I am trying to do something simple but not quite sure how to do it. I am using the GetImportFileName and then selecting information of one sheet from that file and copying to another workbook. I need to know how to then get back to the file I have opened to search the next sheet and so on. The problem I have is with this line.
Workbooks(FileName).Activate
Sub GetImportFileName()
Dim Finfo As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant
' Set up list of file filters
Finfo = "All Files (*.*),*.*"
' Display *.* by default
FilterIndex = 5
' Set the dialog box caption
Title = "Select a file to Import"
' Get the Filename
FileName = Application.GetOpenFilename(Finfo, FilterIndex, Title)
' Open the Filename
Workbooks.Open FileName
WPD
End Sub
When I press the button , i would like to open PDF file. The path is "\ engineeringABC.pdf"
View 2 Replies View RelatedWriting a macro to run SOLVER on a linear program. I have twenty tabs, each with a linear program and a solver already set up (constraints, cells to change, and cell to maximize). I would like to run a sub that will solve each tab's LP by running the tab's SOLVER...
If it makes it easier, my cell to maximize is I17, by changing B16:H16, subject to I20:I40 SolverAdd -> SolverOk -> SolverSolve
I have the code below to add my new sheet and give it todays date (coming from the menu sheet). However I can not figure out how to add the before code so it will save the new sheet at the beginning of all of the sheets every time.
[Code] .....
I have some VBA code that allows the user to open a linked powerpoint presentation from an Excel workbook. It works well but it does not ask the the user to update links so if the Excel book has changed the powerpoint stays the same. I am very new to VBA so I tend to copy things that work but don't always know why! Anyone out there know how to get it to update links automatically or at least ask the user to do so? Here is the code:
View 2 Replies View RelatedI would like to open a certain file everyday. However each day this file is created and named with the date, for example Production_FixYYYYMMDD.csv, in VBA how do I accomplish this? The alternative I could think of is to create a .bat file to copy that file over to a separate directory and rename it Production_Fix.csv and just use that, but if that batch file fails to run that would cause issues.
View 4 Replies View RelatedAny idea why a file in my XLSTART folder does not open when I start Excel?
I had a cell in a file (not one of the files in XLSTART folder) that was refered to by thousands of other cells. I clicked on Trace Dependents and Excel then spent the next 10 minutes trying to point to all the cells. I eventually gave up and used the Windows Task Manager to close Excel. Ever since then Excel doesn't open 1 of the 3 files I have in the XLSTART folder.
I have a 14.3 MB xlsb file that I am trying to open with no success as it just hangs. Anyone have any ideas on how to get the data out of there? I would like to be able to get the data to a pivot and copy the pivot to another file to try and reduce the load on one file. I tried to import to Access but was told I was using a file version before 2007, which is impossible since it is a xlsb file.
View 9 Replies View RelatedI am having a problem that shouldn't be that hard to fix, but I can't seem to figure it out. I have a file in the xlstart folder, called "personal.xls" with macros in it. The security level is at low, it isn't on the disabled list, and it won't automatically open when excel opens. If I double click on it, it opens. It just won't open when excel is first started. Is there any way to correct this?
View 2 Replies View Related