Best Way To Import VBA (or equivalent functionality eg macro) in Excel

[LaCour]

To remove funky newline characters after exporting data from Access, I have the following line of VBA.

Cells.Replace What:=vbCr, Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

I am creating a command button and placing the code there. I would prefer to avoid messing with vba every time I want to execute the replace. Is there an easier way to do this? The main interest is allowing the user to use the replace functionality without having to mess with vba.

I am open to vb scripts as well, as I could kick that off without the user knowing.

Let me know if you have thoughts.

Thanks
Blair

 

[xlbo]

And what's the difference between vb scripts and running VBA ???
Your best bet is to create an add-in which creates an extra menu. Attach the replace code to that new menu item

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[LaCour]

If I do it in a script, it will happen in the background without the user knowing. If I do it in VBA, the user has to engage and actively run the code.

I am generating the excel spreadsheet through VBA in access ... in that same procedure, I would like to run a script that will do the same thing that the VBA would do.

Regarding the vbScript, if I am running the following in VBA, what would I need to do to have the same thing work in a script. I imagine I will need to cite the excel file and set an object = to it and go from there.

Cells.Replace What:=vbCr, Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False


Let me know how to run this in a script that I will trigger from within VBA.

Thanks
Blair

 

[LaCour]

Better yet, is ther a way to run

Cells.Replace What:=vbCr, Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

in the VBA in my Access database? I should be able to reference the excel file and do from there.

Let me know what you think

Thanks
Blair

 

[steveouthwaite]

Hi LaCour

I've never had any need to do any replace after exporting, I'm assuming you're not exporting to an .xls file, then opening in excel
anyway... you can do excel stuff in access vba...
here's a snippet from one of my databases that exports to a spreadsheet, then does some formating in excel...

Private Sub save_as_excel_spreadsheet(path)

If Dir(path) <> "" Then
Kill path
End If

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
    "Alcohol_Licence_Holder_Report_Table", path, True


Dim xlapp As Excel.Application
Set xlapp = New Excel.Application
xlapp.Workbooks.Open path

With xlapp

.Cells.Select
.Selection.Font.Name = "Arial"
.Rows("1:1").Select
.Selection.Font.Bold = True
.Cells.Select
.Cells.EntireColumn.AutoFit
.Selection.WrapText = True

.Columns("B:B").Select
.Selection.EntireColumn.Hidden = True
.Rows("1:1").EntireRow.AutoFit

.Columns("G:G").Select
.Selection.Insert Shift:=xlToRight
.Selection.ColumnWidth = 5.29

.Cells.Select
.Selection.Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=True, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

.ActiveWorkbook.Save

.Quit
end with


set xlapp = nothing

you could use something like:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
    "Your export table", yourpath, True


Dim xlapp As Excel.Application
Set xlapp = New Excel.Application
xlapp.Workbooks.Open yourpath

xlapp.Cells.Replace What:=vbCr, Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

xlapp.ActiveWorkbook.Save

xlapp.Quit

set xlapp = nothing

hope this helps


SteveO

 

[SkipVought]

Hi,

If I do it in a script, it will happen in the background without the user knowing. If I do it in VBA, the user has to engage and actively run the code.

This is an incorrect assumption. VBA can run both without the user's initiation and knowledge. VBA code can be event driven.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]