By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,646 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Working on excel sheet from Access

P: 1
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.
Jul 23 '07 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 634
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

Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2.     Dim oExcel As Object
  3.     Set oExcel = GetObject(, "Excel.Application")
  4.  
  5.     If Err.Number <> 0 Then
  6.         Err.Clear
  7.         Set oExcel = CreateObject("Excel.application")
  8.     End If
  9.  
  10.     If Err.Number <> 0 Then
  11.         MsgBox "Could not open Excel!", vbCritical
  12.         Exit Sub
  13.     End If
  14.  
  15.  
  16.     oExcel.Workbooks.Open ("FilePath&NameToOpen")
  17.  
  18.  
  19.     If Err.Number <> 0 Then
  20.         MsgBox "Could not open workbook '" & FileToOpen & "' !", vbCritical
  21.         If oExcel.Workbooks.Count = 0 Then oExcel.Close
  22.         Exit Sub
  23.     End If
  24.  
  25.     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
Jul 23 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.