Need help with automation

Topic

New Reply

I need to save a file under a new name in a specific place, then save it again as a html file. When I record a macro to that purpose and then execute the macro, it stops for both file saves and asks if I want to replace the file(s) already there. I do in both cases.
Also, I then want to go back to file 1 and create a range name for the current region. I do not seem to be able to do the range name correctly.

If anybody can point me in the right direction I would appreciate it.

Thanks,

itconc

Reply | Quote

Replies

Hi itconc,

Please post the relevant portions of your code, so that we can see how you’re doing the saves.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

here it is:
Sub ex()
‘
‘ ex Macro
‘

‘
ChDir “C:ArbeitCASData”
ActiveWorkbook.SaveAs Filename:=”C:ArbeitCASDataNewData.XLS”, FileFormat _
:=xlNormal, Password:=””, WriteResPassword:=””, ReadOnlyRecommended:= _
False, CreateBackup:=False
ActiveWorkbook.SaveAs Filename:=”C:ArbeitCASDataNewData.htm”, FileFormat _
:=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False
Columns(“D:D”).Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=1
Columns(“F:Q”).Select
Selection.Delete Shift:=xlToLeft
Range(“A1″).Select
ActiveWorkbook.Save

‘
ActiveWorkbook.Close
Workbooks.Open Filename:=”C:ArbeitCASDataNewData.XLS”
Columns(“F:F”).Select
Selection.Delete Shift:=xlToLeft

Range(“A2”).Activate

ActiveWorkbook.Save
‘ActiveWorkbook.Close
End Sub
For the rangename I had this:
Sub Macro3()
‘
‘
‘
‘Range(“A1:Q19″).Select – I remarked this out.
‘
ActiveWorkbook.Names.Add Name:=”newdata”, RefersToR1C1:=selction.CurrentRegion.Select

Selection.CurrentRegion.Select
Application.Goto Reference:=”newdata”
End Sub

Thanks,
itconc

Reply | Quote

What you need is:

Application.displayalerts = false
{your save code}
Application.displayalerts=true

For the rangename try:

Activeworkbook.names.add Name:=”your name here“, _
RefersTo:=selection.currentregion.address(,,xlR1C1)

Note: the above assumes your cursor is located within the current region! If not you need to locate it there first or do a selection of the current region first then delete the .currentregion from the above code.

Note 2: Don’t miss the two commas in the arguments to the Address property! If you want you can replace that with .address(ReferenceStyle:=xlR1C1)

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Looks great. It works in Excel. I will need to thenimport to Access – that is properly automated and works.
Thank you so much for your help!
ITconc

Reply | Quote

I am having a problem with the range name. It works the way you suggested. However, during the import, Access does not recognize the range name because it only shows up under insert/name/define not in the regular range name list.
I am not sure if this now goes under Access.
Thanks,

itconc

Reply | Quote

Itconc,

Can you post your Access Import code?
I tested the create range name code on an excel sheet I have and the name showed up when I tried to use Names,paste.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I see the name under names paste also. However, it does not show up in the name box. If I press Ctrl ain excel and name the name the range through the name box, all is well.
I am attaching the error code I get in Access, the macro and the vba code.
Thanks,

ITconc

Reply | Quote

I think you’ll find that this line:

Activeworkbook.names.add Name:=”your name here”, _
 RefersTo:=selection.currentregion.address(,,xlR1C1)

should be:

Activeworkbook.names.add Name:=”your name here”, _
 RefersToR1C1:=selection.currentregion.address(,,xlR1C1)

Reply | Quote

I think you’ll find that this line:

Activeworkbook.names.add Name:=”your name here”, _
 RefersTo:=selection.currentregion.address(,,xlR1C1)

should be:

Activeworkbook.names.add Name:=”your name here”, _
 RefersToR1C1:=selection.currentregion.address(,,xlR1C1)

Rory, Right your are, it should be! Interestingly it will work the other way at least in 2003 it still generates the same reference for the name!

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote