Working on excel sheet from Access

[gpgp]

I have a excel macro like this:

1. Range("C2").Sel ect
Range(Selection , Selection.End(x lDown)).Select
Selection.Repla ce What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xl ByRows, MatchCase:=Fals e, SearchFormat:=F alse, _
ReplaceFormat:= False
Selection.Numbe rFormat = "[$-409]d-mmm-yy;@"
Selection.TextT oColumns Destination:=Ra nge("C2"), DataType:=xlDel imited, _
TextQualifier:= xlDoubleQuote, ConsecutiveDeli miter:=False, Tab:=True, _
Semicolon:=Fals e, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNu mbers:=True

2. Columns("M:M"). Select
Selection.Inser t Shift:=xlToRigh t
Selection.Inser t Shift:=xlToRigh t
Columns("L:L"). Select
Selection.TextT oColumns Destination:=Ra nge("L1"), DataType:=xlDel imited, _
TextQualifier:= xlDoubleQuote, ConsecutiveDeli miter:=False, Tab:=True, _
Semicolon:=Fals e, Comma:=False, Space:=False, Other:=True, OtherChar _
:=":", FieldInfo:=Arra y(Array(1, 1), Array(2, 1)), TrailingMinusNu mbers:=True


Can you please suggest me the replacement function or the code for above in Access.

[MikeTheBike]

Hi

I have a excel macro like this:

1. Range("C2").Sel ect
Range(Selection , Selection.End(x lDown)).Select
Selection.Repla ce What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xl ByRows, MatchCase:=Fals e, SearchFormat:=F alse, _
ReplaceFormat:= False
Selection.Numbe rFormat = "[$-409]d-mmm-yy;@"
Selection.TextT oColumns Destination:=Ra nge("C2"), DataType:=xlDel imited, _
TextQualifier:= xlDoubleQuote, ConsecutiveDeli miter:=False, Tab:=True, _
Semicolon:=Fals e, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNu mbers:=True

2. Columns("M:M"). Select
Selection.Inser t Shift:=xlToRigh t
Selection.Inser t Shift:=xlToRigh t
Columns("L:L"). Select
Selection.TextT oColumns Destination:=Ra nge("L1"), DataType:=xlDel imited, _
TextQualifier:= xlDoubleQuote, ConsecutiveDeli miter:=False, Tab:=True, _
Semicolon:=Fals e, Comma:=False, Space:=False, Other:=True, OtherChar _
:=":", FieldInfo:=Arra y(Array(1, 1), Array(2, 1)), TrailingMinusNu mbers:=True


Can you please suggest me the replacement function or the code for above in Access.

First you need to use Automation to open an instance of excel and then open your workbook in that instance, somethink like this

On Error Resume Next
    Dim oExcel As Object
    Set oExcel = GetObject(, "Excel.Application")

    If Err.Number <> 0 Then
        Err.Clear
        Set oExcel = CreateObject("Excel.application")
    End If
    
    If Err.Number <> 0 Then
        MsgBox "Could not open Excel!", vbCritical
        Exit Sub
    End If
    
    
    oExcel.Workbooks.Open ("FilePath&NameToOpen")
    
    
    If Err.Number <> 0 Then
        MsgBox "Could not open workbook '" & FileToOpen & "' !", vbCritical
        If oExcel.Workbooks.Count = 0 Then oExcel.Close
        Exit Sub
    End If
    
    On Error GoTo 0

Then all your excel code need Refering to the excel object and/or workbook object.

Also, if you want to see excel at the end it has to be visible (oExcel.Visible =True) !?

BTW the obove uses late binding to get any open excel application if there is one, if not, it opens a new one.

I think that should keep you busy for a start.

Good luck


MTB