Working on excel sheet from Access

[gpgp]

I have a excel macro like this:

1. Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.NumberFormat = "[$-409]d-mmm-yy;@"
Selection.TextToColumns Destination:=Range("C2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

2. Columns("M:M").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("L:L").Select
Selection.TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=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").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.NumberFormat = "[$-409]d-mmm-yy;@"
Selection.TextToColumns Destination:=Range("C2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

2. Columns("M:M").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("L:L").Select
Selection.TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=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