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 have the following code that is going to trigger an export macro and an import macro allowing the user to upgrade the version of their report;
x = MsgBox("Is the file you wish to export from open?" & vbCrLf & vbCrLf & "If it is click yes" & vbCrLf & "If its not click No" _ & vbCrLf & "If you need to abort the macro click cancel", vbYesNoCancel)
Select Case x Case 6 MsgBox "Please use the Windows menu in Excel to ensure that the file" _ & "you wish to export from is the ActiveWorkbook!", vbInformation ' Missing step If ActiveWorkbook.Name = ThisWorkbook.Name Then Exit Sub ExpWb = ActiveWorkbook.Name Case 7 On Error GoTo Handler .......................End Select
Problem is, I have a missing step within Case 6 which allows the user to ensure that the file they are exporting from is the ActiveWorkbook.
I am looking for macro that when run, will open a file explorer window and prompt the user to select a folder and file where they have data stored. Then I need it to be stored as a variable and used as a part of a "Workbook.Open Filename" command.
The reason for this is that, I have a huge formatting marco stored within a workbook. When a user extracts a report from SAP, I want the workbook to grab the file that is extracted, open it and import all of the data in order to be formatted.
I am using an XY scatter chart that displays the planned path and an actual path as I go. The planned path is set up in 100' increments and goes 12000' and actual path varies along the way.
My data that I use is the Northing (ft), Easting (ft) and Vertical Section (ft) for both the planned path and actual path.
I was wondering if there was an east way to get that information from the chart or if there is a formula that would allow me to calculate distance between the two points by using the data I use for my chart.
I noticed all the code that i have on a userform using activeworkbook will relate to whatever the last active workbook was "who would have guessed hay" Seeing thisworkbook used a lot i decided to go through the whole project and change all activeworkbook too thisworkbook.sheet1 During this i also made a few other changes that i was confident in, When i run it now it errors on the thisworkbook.sheet1. Is this the wrong way of using thisworkbook to make sure the code always deals with the correct file. (will this ever be affected if the file goes through a Save As) Or is it during my over conifedents that i have made a error somewhere else and the debuger is point me here cos it hates me.
I have a userform that presents the user with 4 choices and once they click on one choice a macro runs that filters and formats the data and when the user clicks on the button start over it will reopen the file they were just using prior to the changes occurring. The reason for this is the same spreadsheet is printed up to 4 times with different information (remember the part where the data was filtered?). So basically the user clicks on option 1 the macro runs formating the page and then the user clicks start over (different userform then the first one) and the spreadsheet closes and reopens before the changes.
The way I have excel reopening the file now is by having the user input the file name into a text box and clicking OK this passes the file name to the open code in VBA. How can I tell the code the name of the sheet after the sheet is no longer active (because a userform was made active)? Basically the end goal is for the user to click over and excel will just reopen the file without any user input.
But i want to adapt the "ActiveWorkbook.Sendmail" so I can attach my current workbook as it is, to the email.
Private Sub CommandButton10_Click()
Dim OutApp As Object Dim OutMail As Object Dim strbody As String Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = "firstname.lastname@example.org" .CC = "email@example.com" .BCC = "" .Subject = "Testing send to multiple emails." .Body = "Please review." & vbCrLf & vbCrLf & ActiveWorkbook.FullName .Send 'or use .Display End With On Error GoTo 0 Set OutMail = Nothing Set OutApp = Nothing
Dim Msg, Style, Title Msg = "E-mail has been sent " & Chr(13) & Chr(10) & "Press OK to continue." Style = vbOKOnly + vbInformation Title = "Open Issues List" Response = MsgBox(Msg, Style, Title, Help, Ctxt)
I run the macro on a currently open workbook (name could be anything) and open another workbook (name known) and step through the sheets copying various blocks of data across to the first workbook. I need to Activate the 2 workbooks alternatively as I move data back and forth and move from sheet to sheet. What I can't work out is how to determine in code what the "unknown" workbook name is so that I can make sure I activate the right workbook in the code.
I tried Activating the workbook with the "unknown" name and setting a variable = ActiveWorkbook.Name. However the value of this variable changes during the code run when you activate the other book. Is there any way to collect this value and "freeze" it so it won't change as the code runs? At the moment I have got round it by ensuring the "unknown" book is the only one open before I start and refer to it as Workbooks(1) in the code.
I am trying to build a macro to be placed in a blank spreadsheet for use as a template.
My development platform is Excel 2003 on Windows XP SP2. The script was initially recorded as a macro against a single data file with absolute range references and worked both on my develpment platform and on the production platform (Excel 2000 on Windows 2000). However, I have been having fits trying to convert it to dynamic addressing for the PivotCaches.Add. I develop Excel solutions a few times a year and this pivot table is the deepest I have dug into OLE code, so I am not an expert and could have a simple problem. I included the entire macro because I am unsure if there are sideeffects to what some of it is doing. I am getting a 1004 error "The PivotTable field is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field."
Since I do not get this error with the absolute references, I am puzzled as to what to fix. That is another reason for supplying the entire macro: ...
I have a workbook that is used by many people on a network- I am editing and saving data throughout the day, and the other users are constantly having to close and re-open thier file in order to have the most recent data. today i came across the '.UpdateFromFile' command in the help file, which would be perfect, if i could only get it to work. im guessing the problem is the expression, which would be the active workbook, but' ActiveWorkbook.updatefromfile' is not working. The file is on the network, remember, im guessing this is the problem. So, im also guessing i just need to get the syntax right for the filename.
I have used a code to find the character "-" and replace it with nothing ("") from several named worksheets across my workbook, which OzGrid helped me with before, and it worked fine. Now I've added the code into my module (at the bottom) it doesn't work. The rest of the code works fine, and it doesn't come up with any errors when I run it, but it just seems to ignore the 'find and replace' part of it.
Dim fromSheet As Object Set fromSheet = Worksheets(1) Dim fromRow As Integer Dim toRow As Integer Dim lastRow As Integer Dim errCount As Integer Dim thisSectionName As String Dim i As Integer ' loop counter Dim rng As Range 'found range Dim emptyCellAbove As Range Dim rng1 As Range Dim arng1 As String 'Address of 1st hit rng1 Dim ws(7) As String Dim ii As Integer '2ND LOOP COUNTER Dim iii As Integer '3rd Loop Counter
This piece of code successfully replaces a module in a range of workbooks -
Sub CopyOneModule01() Const strModName As String = "Module1" Dim FName As String Dim vWbkNames As Variant Dim lLoop As Long vWbkNames = Array("CFADS01", "CFADS02", "CFADS03", "CFADS04") With Workbooks("CF266") 'copies from here FName = .Path & "code.txt" .VBProject.VBComponents(strModName).Export FName End With...........................
I recorded the following macro to select all the worksheets in the Activeworkbook by clicking on the first worksheet and then hitting Shift Tab and selecting the final worksheet (thus '[Group] selecting' all worksheets in the active workbook).
I'm developing an excel addin using Visual Studio 2008. At the moment i'm trying to display a popup showing the value contained in cell A1. However, i'm getting a runtime error saying that xlWorkBook is null. I've been able to get this to work if i open a workbook but i want to be able to execute my code on the currently active workbook. I thought that ActiveWorkbook (also tried ThisWorkbook) would let me do this but i'm having no success.
using Excel = Microsoft.Office.Interop.Excel; using Office = Microsoft.Office.Core; ... ... Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet;
I do not want ThisWorkBook to close prematurely whenever the specified Activeworkbook is closed without saving because there are other tasks to complete. The Workbook_BeforeClose dun seem to fix it for my case below.
Here’s my situation. I want to copy data from a range of a particular protected sheet in 5 (password-protected) workbooks of a similar format, to 5 worksheets(unprotected) in ThisWorkBook, which is the Master Workbook. The password of the protected sheet is common to the 5 workbooks. The passwords of the 5 workbooks are contained in a sheet in ThisWorkbook. After copying the data from Workbook1 ( 1 of 5), ThisWorkbook are prompted for closure, when Workbook1 is closed without saving. Responding to the prompt to save or not to save, forces ThisWorkbook or MasterWorkbook to break the For-Next loop. By setting Cancel = True in Workbook_BeforeClose, I can stop Thisworkbook from closure, but I will still break the For-Next loop. I have considered the method of copying data from a closed workbook, but I dun think VBA allows copying or referencing a closed workbook which is password protected at workbook and worksheet level.
Here’s my coding :
Dim MasterWB As Workbook Dim TempWB As Workbook Dim Source As String
Set MyWB = ThisWorkbook For i = 1 to 5 Set TempWB = Workbooks.Open (Filename:=WB(i), Password:=password(i) ) TempWB.Sheets("ProtectedSheet").Unprotect Password:="SheetPasswd" MyWB.Sheets(i).Range(XXX).Value = TempWB.Sheets("ProtectedSheet").Range(YYY) TempWB.Close False ‘dun want to respond toclose ThisWorkbook else it breaks for-next loop Next i
Is there a way too get the SaveAs box come up with a marco? When you go too file and save, a box comes up and lets you choose where and what you save. I would like too have it come up with a command button if its possible.
I'm running a macro in a template and trying to save the results to a different folder. I want the file name to be blank in the Saveas Text box of the UI also. Unfortunatley my code is bugging out & I can't work out why.
It is supposed to bring up an input box, the date entered in input box becomes the date of file. The file is then Saved As. This all seems to work but when I try to open the newly saved file the sheet does not open. Is this a file type issue? Should it be .xls?
Code so Far
Dim InputDate As Variant Dim FileType As String Dim myFileName As String Dim myFolder As String
An already existing file will be changed by my macro and at the end I want the user to save the file with a different name in a different directory with the option to change the filename or directory. Therefore I use the excel-standard-dialog "save as". But I want the dialog to start in another path as the path the original-file is stored in. But also I use the ChDrive and ChDir Parameters the dialog even starts in the original-file-path. I think this is standard for the dialog and I should maybe use a parameter in the show-arguement? The code is below.
Option Explicit Sub SaveMyFile() Dim xFileName, xAnswer ChDrive "J" ChDir "J:myfolder" xFileName = "testme.xls" xAnswer = Application.Dialogs(xlDialogSaveAs).Show(xFileName) End Sub