I have written the following simple macro to import some data into a worksheet and then prompt the user to save the file in Excel 2003 format (the system to which we will upload this data does not accept formats later than 2003). The template is in "*.xlsm" format.
The code executes without error, but when the user hits the "Save" button in the "Save As" dialog box, nothing happens. The "Save As" box closes, but no file is saved.
Code: Private Sub cmdImportData_Click() Dim sFName As String 'On Error Resume Next PrepData CopyData FormatColumns 'prompt the user to save the file in "*.xls" format sFName = Application.GetSaveAsFilename("upload", "Excel files (*.xls), *.xls") End Sub
I have written a Excel (2003) that searches a worksheet for a string in any cell. If the string is not found, it uses the 'On Error GoTo' command to jump to a given label. It works fine on the first string not found. When it searches for the next non-existent string, it fails with:
'Run-time error '91': Object variable or With block variable not set'
Do I have to clear a buffer after each cells.find search?
I'm not sure why this is happening, but every other time I run this one specific macro, I get a "Run-time error '1004': Paste method of Worksheet class failed". I even tried running this macro, then running a different one, then running this again, but I still got the error every other time.
Every time I get the error, it highlights this line of Sheets("Regenerate Request").Paste
This is all of the code up to where I get the error:
Sub YesRegen() ' after user has hit Yes on the RegenerateRequest macro, this posts the new request to ' the log, generates the new file and attaches it to an email
I'm trying to create a macro that will take a worksheet from an Excel workbook, and save it as a text file, with the name determined from user input, and the location being the desktop of the currnet user's computer.
First I use a function, (provided by RoyUK), to get the name of the current desktop, then I try to use that name, along with my user input, so save the file. Obviously, it's not working, or I wouldn't be here. I get a "Run-time error '1004': The file could nto be accessed". I think it's just a syntax issue with the file name, because debug takes me to the "SaveAs" portion of the macro.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application. ScreenUpdating=False ' Cancel user prompted save procedure Cancel = True ' Code removed... changes some values and settings before saving ThisWorkbook.Save ' Code removed... return the original values and settings Application.ScreenUpdating=True End Sub
For some reason, the workbook is not being saved when the above code is executed. It seems the problem lies with "ThisWorkbook.Save"... in that it just isn't saving! If I delete "Cancel=True", the workbook will save (obviously!).
I have spent an hour trying to work this out and have come to the conclusion that it is something other than my code... has anyone experienced this problem before?
When I use this code in the workbook I want it gets as far as creating a new workbook and pasting the data, but then an error comes up saying nothing but "400", and does not complete the saving part.
Private Sub Workbook_Open() Sheets("Open").Activate End Sub Sub SaveASheet() Dim fName As String Dim myPath As String Dim sht As Worksheet myPath = "K:3. DesignSupply OrdersLog" For Each sht In ThisWorkbook.Worksheets If sht.Range("D1").Value "" Then sht.Copy With ActiveWorkbook .SaveAs myPath & ActiveSheet.Range("G11").Value & ".xlsx" .Close End With End If Next sht...................
I've got some code that reformats a file that a supplier sends us. They seem to randomly change the structure, so I'm trying to make the code a bit more robust.
Part of the code uses MATCH to look for specific column headings, so I've got some error handling around this of the format:
On Error GoTo NoIncCol Line using MATCH On Error GoTo Exiting where Exiting is my standard error handling (Close without Save) and NoIncCol is the same, but with an error message saying that the term the code was trying to MATCH can't be found. This works well for me.
In another part of the code, I need to check for another column heading, which so far has turned up in two different forms. At the moment I'm doing this as follows:
With Sheets("Sheet1").Range("1:1") Set rFound = .Find("Term1") If Not rFound Is Nothing Then a = "Term1" Else
This feels a bit clunky, and I don't really like having what is essentially error-handling in the middle of my code. But the only other way I can think of to do it is to use a GoTo, and I believe that's not really considered good practice either.
I need to:Turn off "save" and "save as" command barsPrompt user for a unique filename Save file to a specific directory on the network common to all "p:dataprc"Requery user if filename exists and if they DON'T want to overwrite.Return to worksheet (there is only one) if the user cancels from the message box.Turn commandbars back on The macro runs from an on screen button I get bug errors on NO or CANCEL when clicked in the message box. This is my current
Private Sub Workbook_Open() Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save As...").Enabled = False Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save").Enabled = False End Sub
Sub SaveMe() ' ' Keyboard Shortcut: Ctrl+z ' Dim BaseDir As String Dim NewName As String BaseDir = "p:dataprc" ...........
I have a workbook that retrieves data from a file from a URL address upon opening. Actually, I am saving the URL file to a local drive and then updating my workbook with this data. The problem is that the job that updates the URL file sometimes fails. I want to notify the user of my workbook that the data has not been update recently. I am trying to use the built in document properties (Last Save Time) but I cannot get it to work except for the workbook that has the macros in it. Is there a way to find the last save time of a workbook (from a URL address) seperate from the workbook that has the marco? Below is my current code that errors out at:
dp = Application.Workbooks("DockReportExport.xls").BuiltinDocumentProperties("Last Save Time") with and runtime error.
Sub FTP() Application.DisplayAlerts = False Application.ScreenUpdating = False 'Open file from URL addres to check last save time Workbooks.Open Filename:="http://172.16.1.94/Files/Operations/...portExport.xls" 'Check if data on server has been updated in the last 15 minutes
I have a file that sits open all the time, and performs some refresh functions every thirty minutes. I need the file to save a copy of the tab as a CSV file at a given time interval. The code below is almost there, just need to work with the time interval part. The way it should work is to open the csv, copy / paste the active sheet; then close the csv; leaving the original excel file open. I can run it, and it works, but the time interval is not triggering.
I can get the time interval to work by itself, and the save csv part to work by itself also; I need them to work together.
VB: Sub test() Application.OnTime Now + TimeSerial(0, 1, 0), "test" Dim OutputFile As Workbook, InputFile As Workbook Dim sDD As Worksheet
I am trying to find a way to sort this info by column D but within the Groups shown. So, I want to sort rows 5 to 13, then rows 15 to 19 and then rows 21 to 29.
However I need to a macro to do this because the figures are updated weekly and the order will change. Furthermore, there could be new depts added when the figures are updated, so the row numbers may also change....
I have a relatively complex report that I work with and a worksheet is no longer required. I have deleted the worksheet and reference to it hwoever when running the macro to pull all the data, it gets to the summary of all the data and i get the Run Time Error 1004 Application-defined or object-defined error pop up. ON reviewing it, it is on this line ActiveCell.Offset(0, 0).Range("a1:a" & Range_Height).Select of the below code...
VB: Sub GetRangeName() Sheets("TOTAL").Select
use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window.
I have a simple function below to put in different forumlas in different cells to get stock quotes. When I run this I get runtime error 1004 application-defined or object-defined error. The first formula goes through but vba chokes on the next formula: ActiveCell.Offset(I - 1, 4).Formula = username
For some reason my form won't open when the workbook is opened. I get an error message "run time error '424' object required" (which happens when I have Form1.show in the BOTH workbook_open event and the userform_initialize event (oops)). When I removed form1.show from the userform_initialize I don't get an error but I also get no form. I recall having this issue before but I can't recall how to fix it.
Can I call the userform_initialize event from the workbook open event to get around this successfully and properly?