Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Excel Macro Question

Nov 3, 2005 9:14PM PST

I am recording and editing a macro in Excel. During the sequence of actions, a save confirm window opens up, to which I click yes.

This does not seem to get recorded. I vaguely remember there is a line you have to insert in the macro, something like "GETKEY", but I can't work out the syntax.

Can anyone help me ?

Robert

Discussion is locked

- Collapse -
Re: Excel macro
Nov 3, 2005 10:56PM PST

It should be possible to make/change the macro so that it doesn't ask for confirmation. Please explain what you want to do, and supply the piece of code the macrorecorder generates. Then we know where to look for the refinement needed. Just not enough information now.

Kees

- Collapse -
Re: Excel Macro
Nov 3, 2005 11:51PM PST

What is recorded is as follows:-

Sub temp1()
'
' temp1 Macro
' Macro recorded 04/11/2005 by rcgyuk
'

'
ChDir "C:\temp"
ActiveWorkbook.SaveAs Filename:="C:\temp\TEMP.CSV", FileFormat:=xlCSV, _
CreateBackup:=False
ActiveWindow.Close
End Sub

When you run the macro, I have to answer yes to "A file named c:\temp\temp.csv already exists, do you want to over write ?"

I then have to answer yes to " Temp.csv contains features that are not compatible with CSV. Do you want to keep the workbook in this format ?"

I then have to answer No to "do you want to save changes ?"

I used to have a macro that answered these 3 questions for me, but I have lost it.

- Collapse -
Re: Excel macro
Nov 5, 2005 5:45AM PST

Rgcyuk,

I didn't yet have the time to try this myself, but the command (method in fact) you're referring to, I think, is Sendkeys, not Getkey. Some information (there must be also in the Excel VBA Help):
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/office97/html/output/F1/D3/S5A796.asp

The Saveas-method is documented on the same page (click on it in the left column). It has a lot of parameters but none to provide this answers to the two questions.

Close however has an option to indicate you don't want to save the changes, so you can skip that question by programming the "No".

I may note that if the file c:\temp\temp.csv in this example doesn't yet exist, there's only one question to answer, not two, and that makes a difference in the number of answers to send. I can't tell you how to find out beforehand, so you have to make sure it's present some other way.

Hope this helps. Please let me know if it doesn't.


Kees

- Collapse -
And ...
Nov 5, 2005 7:06PM PST

how to find out if the file already existst.

Good old Basic, still working in VBA. Check the syntax, this isn't exact at all

on error go to not_exists
open #1 "file" for input
sw_exi=true
close #1
continue:


not_exists:
sw_exi = false
go to continue

- Collapse -
suppress alerts with vba
Nov 7, 2005 8:58AM PST

These lines will save the active workbook without displaying any alerts - make sure you're sure about what you're doing beforehand; if a file exists with the same path and filename, it WILL be overwritten.

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= "path\filename"
Application.DisplayAlerts = True

There is also the SendKeys Method.
for example - insert this line in a sub to open the "find" dialog box.

Application.SendKeys ("^f")

- Collapse -
Re: SendKeys
Nov 21, 2005 12:40AM PST

I got my macro to work using the SendKey function. I did run into one strange thing though.

When I save the file with the macro, the column with the dates in it has the months and days reversed. This completely confuses the application that then tries to import the file.

If I save the file manually, this doesn't happen.

I got around this by recording a macro to reset the date format before saving the file, but I am curious as why it happens in the first place.

I have only seen this happen with one other macro, where I have a custom menu with 5 macros attached, to set the cursor direction on enter (left, right, up, down or no_move), and running the macro causes comments to become invisible, and I had to add a line to make them visible again.

Robert