I created folder on my desktop to hold various documents. I have master excel document which contains forms and command buttons that open documents from same folder. Is there a way to change code instead of having full file path to specify current directory. I'm worried that if I move this folder to another location then my open file button will not work since location is changed.
Also when I open with command button word document, I have a button on word document to close. I can close document but word application is still active. Can I close word application with the document at the sam time. This is the code I'm using: "ActiveWindow.Close"
I am continuing to struggle with my first attempt at a macro, and wonder if someone would mind taking a look to see where I have gone wrong? All I am trying to do is create file copying tool where the user can define the source and destination directory in workbook cells, and assign the macro to a button to complete the copy of all files from source to destination.
The macros do this:
Source and Location directory defined in Sub Archive1. These are passed to file copy macro Sub ArchiveScript. This macro checks for presence of destination directory, ensures that the directory backslash is in place, specfies the variables fn and fn2 to make sure the filecopy command copies all files in the directory, and then carries out the filecopy function itself.
However, I am getting two errors from the code that I can't figure out (I am still very much a beginner!)
- Invalid procedure call or element on fn = dir() at the bottom
- The copy still works, but only seems to copy one file from the source to the destination, and not all of them.
I wanted to run a batch file which does something like,
Dir /b >Test.csv Start Test.csv
And then runs an Excel macro once the workbook is open.
I looked into this and discovered that it’s not really that possible to run an excel macro through batch file but other options were to use ‘Workbook_Open’ etc.
I thought this might cause problems because Test.csv doesn’t contain a macro to auto open and because csv’s usually don’t like macros.
I do however have personal.xlsb which opens every time if that helps.
I am making a macro that requires that user to enter a directory path into a input box and the macro will open the file. Right now it works fine bit i would like to change the code so that instead of using an input box it brings up a box that will allow them to browse files on their computer and then select one to open.
I have a tool file which contains Excel VBA codes. When I click button it asks to select a file. I want that anytime I click the button the dialogue box should open in the folder containg the tool.,
The code bellow allows me to save a copy of the invoice by way of doPDF using invoice number as the name of the pdf to be saved.
Code:
Else Application.ActivePrinter = "doPDF v7 on DOP7:" 'Selects doPDF to genarate PDF file of invoice. ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= "E:LusaGenerated InvoicesINV" & Range("L17").Text & ".pdf", _
[Code]...
As there is the need to, should a quotation or an invoice be wrong, to change it. In other words I have code to cancel the invoice as bellow.
Code:
Dim sResponse As String Dim rFound As Range sResponse = InputBox("Enter record you want to change." & vbNewLine _& "ATTENTION!
This will change the current record number and will erase record data from saved records sheet.")
If sResponse = "" Then --cancelled or OK with no entry: do nothing or add message Else Set rFound = Sheet1.Columns("L").Find(What:=sResponse, LookIn:=xlValues,
[Code]...
The code above simply removes a line on sheet1 to which yet another macro retrieved some info from an invoice such as date, invoice, customer and total.
Now I just realized that when I change an invoice, I will be again when finished, creating another pdf file that already exists.
How can I have the second macro here changed so that it will go to ""E:LusaGenerated InvoicesINV" & Range("L17").Text & ".pdf", _" and delete this pdf file?
I have recorded the below code that functions fine from my computer. My issue is that I need to export/import this macro to other workstations who may have the save to location mapped to a different drive letter. Is there a way to replace the drive letter with the URL (SharePoint)?
ChDir "E:3. CRQsRemedy Dumps" ActiveWorkbook.SaveAs Filename:="E:3. CRQsRemedy DumpsToday_CRQ_8Dump.xls" _ , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Save To Directory: \server_1Dir_1Dir_2FolderToday_CRQ_8Dump.xls Permissions are in place.
What i am trying to do is in 1 workbook (labled as Book1 literally), it needs to copy the sheets out of every .xls file there is in a single directory, we'll call C:MyFolderMySubFolder. There can be anywhere between 1 and 366 files in this particular folder and I need all the sheets in each file labled 'CC' copy that entire sheet, paste that sheet to Book1, go back to that file it was copied from, close it (saving changes is ok), then move on to the next file.. and the next file... and so forth
While pasting into book1, I need each WS copied from each file to paste to a new worksheet in book1 rather than combining them into 1 or overwriting, and lable each of those sheets the file name of which the sheet came from...
The names are in sequence. All files in the folder will be labled as a date such as "9-6-12" so the sheet name in book1 would be named 9-6-12. (so there may result in 366 new worksheets to book1)
Typical user OS will be Windows XP Pro / Win 2K Excel version : 97 / 2002 / 2003
1. Corporate network security settings will only allow directory/subdirectory creation in the “My Documents” section of customers individual computers.
2. Per customer request, VBA application needs to save extracted files for future use.
3. I can specify an initial “My Documents” subdirectory be made and the VBA application file be loaded/copied into that location – i.e. – “My DocumentsCat”.
4. When VBA application is opened from that specified directory, (first time), the application needs to make an additional subdirectory tree to save future files. I can read the opened from location via VBA with the following:
Dim filepath As String filepath = ThisWorkbook.Path As an example – this code would produce a string definition of “filepath” – such as the following:............................
I have numerous sub directories, C:/Jobs/12500 – 12599, C:/Jobs/12600 – 12699 etc, within these directories exist spreadsheets with file names like 12675.xls (in this case it’s in the “12600 – 12699” directory). In my open spreadsheet, in cell A1, there is a job number, say 12675. I want to find and open the existing 12675.xls spreadsheet. If the spreadsheet does not exist, a template (Costing.xlt), in a fixed directory, is opened and then saved, in the correct sub directory i.e. C:/Jobs/12600 – 12699/12675.xls. I am now using the following code, but the directory is fixed. How can it be modified to work with variable directories?
On Error Resume Next Workbooks.Open Filename:="C:Spreadsheets" & Range("A1") & ".xls" If Err <> 0 Then Workbooks.Open Filename:="C:SpreadsheetsCosting.xlt" ActiveWorkbook.SaveAs "C:Spreadsheets" & Range("A1") & ".xls" End If On Error Goto 0
I want to create a macro that will “open the look in list” and stop so I can pick a file to open. I’ve tried to use “record a macro” and “ctrl-o”, but the record a macro won’t stop until I pick a file or cancel the file list. I also tried to use “o” in the short cut key box
I have a Workbook "forecast.xlsx" and several Workbooks which have a individual Number in the Filename (E.g. "3960........xls", "3961.......xls etc.) in the same directory as "forecast.xlsx" + projects"
Now, I would like to browse through "forecast.xlsx" in column H, searching for hyperlinks. In Column H are these Projectnumbers located which are used in the filenames above. Whenever the Cell cointains a hyperlink, following should happen (that far i managed it by myself):
- Store the Cellvalue in a String Variable - Go to the Directory this.workbook + projects - browse this folder, searching for a File which contains the stored string in its filename - open the file - copy a range - close file - This.workbook.Activate - follow the Hyperlink which has been stored as String before - paste selection - continue browsing through column H, looking for the next hyperlink.
I have a VBA which runs bugless, but it copies the wrong range, but to the correct destination.
See the code below:
Sub RESLT_INPUT_ALL() Dim cell As Object For Counter = 1 To 1000 Set cell = Worksheets("Projects overview").Cells(Counter, 8) If cell.Hyperlinks.Count > 0 Then
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
I do routine tasks every day which involves opening 3 different files and pasting data into my main file. The data is always in the same format, and the 3 files are saved in the same location (3 different folders through). The 3 files are saved each day and the naming convention is constant, with only the date changing. For example, the files are always saved in C:My Documents and the files are called test_05.02.2013.xls. Tomorrow the file will be called test_06.02.2013.xls and so on.
So each day I will be rolling a file forward and I want to bring in the info from each of these files based on the new day.
How to do 1, and I will do the others (because the concept will be the same).
I did a macro on my mac to transfer a sheet from one workbook to another worbook. It works very well when the destination workbook is open. Therefore I wanted to add some piece of code to check if the destination workbook is open. If not then I wanted the macro to open it before tranfering the sheet. Here is the code I´m using for tranfering the sheet
Sub Transfer_Sluttet() If ActiveSheet.Index <> Sheets.Count Then Application.DisplayAlerts = False Set ws = ActiveSheet Sheets(ws.Index + 1).Delete ws.Move Before:=Workbooks("Sluttet.xls").Sheets("sheet2") 'Moves active sheet to beginning of named workbook. 'Replace Test.xls with the full name of the target workbook you want. Application.DisplayAlerts = True End If End Sub
This is the type of macro I useually use on my pc to check if a workbook is open and if not then open it
If IsWorkbookOpened("Filename.xls", "C:Documents and ..................
When it opens the desired file, it increments the file name by 1 each time it is opened (via the macro).
Example; The first time it is opened you see the file name in the header read "P'Binder L&T Pages" for a moment, then it changes to "P'Binder L&T Pages1"... I close the file and open it again (via the macro), and see the file name in the header read "P'Binder L&T Pages" for a moment, then it changes to "P'Binder L&T Pages2"... The file name continues to increment on every opening until the PC is rebooted, then it starts at 1 again.
Sub OpnLTpages()
Dim wb As Workbook Dim AlreadyOpen As Boolean
AlreadyOpen = False
For Each wb In Workbooks 'Scan open workbooks If wb.Name = "P'Binder L&T Pages.xls" Then ........................
I am trying to prompt the user for the directory to be saved in and file name to be saved as; then save the workbook in the input directory with the inputted file name.
Code: Sub CopyRow()' 'Copies row to new sheet, highlights it, marks column 'A' as copied. ' Dim cCell As Range Set cCell = Selection.Cells(1, 1) Selection.Copy Sheets("Sheet2").Select Rows("2:2").Select
[Code] .....
Is it possible to modify it to paste into a different workbook called c:filesDestination.xlsm, instead of the existing workbook (Source.xlsm)? The destination sheet name is the same (Sheet2). It's OK if both workbooks are open at the same time.
Is there a way to run a macro in an open workbook when the workbook becomes activated or deactivated? So if I have workbook A open with a macro in it and workbook B open as well, can I have the macro run on workbook A when that becomes the workbook I see on my screen?
how to produce an autostart macro, so when i open the excel file it will go straight to a specific worksheet, or is there another easier way of doing this ?
hi, i need a macro that will open the below file path, and when i select the file i would like the macro to copy the active sheet from the source file being opened from range A2:L10000 and then paste special values to destination CELL A16 of the activesheet of the workbook.
I would like the date format for the below to be generic so it will apply for all months and years.
I 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?
