Acces driven Excel macro (1 Viewer)

K

Kobe2932

Registered User.
Local time
Today, 05:07
Joined
Nov 26, 2012
Messages
25
Hello

Could you please advise what am I doing wrong in the following codes?

1. I'm trying to open an excel file from a given network folder, and insert some codes into it (the code is pretty simpe, just insert a new column in the file)- all from Access. The file is opened, however the code does not run, nothing happens in the xcel file (it is not shared, VBA is enabled in trust center settinges, so I have no idea):

Code: 
Function OpenformatSWP()

    Dim objexcel As Object
    Dim objworkbook As Object
    Dim CodeMod As Object
    Dim LineNum As Long
    Dim Code3 As String
    Const DQUOTE = """"    ' one " character
    Dim destination2 As String
    
    destination2 = "C:\Users\Desktop\test\test.xlsx"
    
    Set objexcel = CreateObject("Excel.Application")
    objexcel.Visible = True
    objexcel.DisplayAlerts = False
    
    Set objworkbook = objexcel.Workbooks.Open(destination2)

    Set CodeMod = objworkbook.VBProject.VBComponents("Sheet1").CodeModule
     
    Code3 = ""
    Code3 = Code3 & " Dim lngLastRow" & vbNewLine
    Code3 = Code3 & " lngLastRow = Cells(Rows.Count, ""A"").End(xlUp).Row" & vbNewLine
    Code3 = Code3 & " Columns(""X:X"").Select" & vbNewLine
    Code3 = Code3 & " Selection.Insert Shift:=xlToRight" & vbNewLine & vbNewLine
    Code3 = Code3 & " Range(""X1"").Select" & vbNewLine
    Code3 = Code3 & " ActiveCell.FormulaR1C1 = ""common_id""" & vbNewLine & vbNewLine
    Code3 = Code3 & " Range(""X2"").Select" & vbNewLine
    
    With CodeMod
        LineNum = .CreateEventProc("Open", "Workbook")
        .VBE.MainWindow.Visible = False
            LineNum = LineNum + 1
        .InsertLines LineNum, Code3
                   
End With

    objworkbook.Save
    objworkbook.close

    'reopen Excel to run autostart
    objexcel.Workbooks.Open (destination2)

End Function

the other thing is that I'm trying to expand the inserted code - not only opening the file but adding a calculated value (concatenate formula) into the newly created column, with the following line

Code: 
Code3 = Code3 & " ActiveCell.FormulaR1C1= ""=CONCATENATE(RC[2],""" - """,ROUND(RC[9],0))""" & vbNewLine

I guess it is jost some syntax error and I have a typo in the hasmarks somewhere, but the macro also stops at this line

thanks for the help in advance
 
K

Kobe2932

Registered User.
Local time
Today, 05:07
Joined
Nov 26, 2012
Messages
25
any advice would be much appreciated ! unfortunately I'm kinda stuck here and cannot go on with further detailing of the macro
 
J

JHB

Have been here a while
Local time
Today, 14:07
Joined
Jun 17, 2012
Messages
7,732
I think your problem lay here, try to change it to, (because there is no Open event in the worksheet, I don't know if it is in a workbook) :
LineNum = .CreateEventProc("Change", "Worksheet")
Click to expand...
 
K

Kobe2932

Registered User.
Local time
Today, 05:07
Joined
Nov 26, 2012
Messages
25
JHB said:
I think your problem lay here, try to change it to, (because there is no Open event in the worksheet, I don't know if it is in a workbook) :
Click to expand...

no success :( if I modify with this, the macro opens up the file, and opens up the Visual Basic window in the excel file, but still nothing else happens, no code is inserted and run on the excel file
 
J

JHB

Have been here a while
Local time
Today, 14:07
Joined
Jun 17, 2012
Messages
7,732
... the macro opens up the file, and opens up the Visual Basic window in the excel file, ....
Click to expand...
Is there an "Change" event available where Visual Basic window opens, (Worksheet)?
By me the code is inserted!

 

Attachments

  • Change.jpg
    Change.jpg
    71.5 KB · Views: 244
K

Kobe2932

Registered User.
Local time
Today, 05:07
Joined
Nov 26, 2012
Messages
25
JHB said:
Is there an "Change" event available where Visual Basic window opens, (Worksheet)?
By me the code is inserted!
Click to expand...

Hi

the good news is that I've got some improvements, the bad news that it gives a different error message now :D

I'm not sure why but access 2010 was unable to insert the code into an xlsx file, had to save the file as xls first.


So here is the updated code I use:

Code: 
Function OpenformatSWP()
    Dim objexcel As Object
    Dim objworkbook As Object
    Dim CodeMod As Object
    Dim LineNum2 As Long
    Dim Code3 As String
    Const DQUOTE = """"    ' one " character
    Dim destination2 As String
    
    destination2 = "C:\Users\test\swp\test.xls"
    
    Set objexcel = CreateObject("Excel.Application")
    objexcel.Visible = True
    objexcel.DisplayAlerts = False
    
    Set objworkbook = objexcel.Workbooks.Open(destination2)
    Set CodeMod = objworkbook.VBProject.VBComponents("Sheet1").CodeModule
     
    Code3 = ""
    Code3 = Code3 & " Dim lngLastRow" & vbNewLine
    Code3 = Code3 & " lngLastRow = Cells(Rows.Count, ""A"").End(xlUp).Row" & vbNewLine
    Code3 = Code3 & " Columns(""X:X"").Select" & vbNewLine
    Code3 = Code3 & " Selection.Insert Shift:=xlToRight" & vbNewLine & vbNewLine
    Code3 = Code3 & " Range(""X1"").Select" & vbNewLine
    Code3 = Code3 & " ActiveCell.FormulaR1C1 = ""common_id""" & vbNewLine & vbNewLine
    Code3 = Code3 & " Range(""X2"").Select" & vbNewLine
    
    With CodeMod
        [B]LineNum2 = .CreateEventProc("Change", "Worksheet")
[/B]        LineNum2 = .VBE.MainWindow.Visible = False
            LineNum2 = LineNum2 + 1
        .InsertLines LineNum2, Code3
                   
End With
    objworkbook.Save
    objworkbook.close
    'reopen Excel to run autostart
    objexcel.Workbooks.Open (destination2)
End Function

I updated the highlighted line as suggested, but this is the result what I gave when running it (code copied from target excel file):

Code: 
Dim lngLastRow
 lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
 Columns("X:X").Select
 Selection.Insert Shift:=xlToRight
 Range("X1").Select
 ActiveCell.FormulaR1C1 = "common_id"
 Range("X2").Select
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub

currently the problem is that the text of the code is not automatically inserted between Private Sub / End Sub, but are separately shown and not running - kinda strange because I have similar functions and the Private sub / End sub lines are properly handled on those.

Could you please show me what was the code you were using ? or what should I update in mine ?
 
J

JHB

Have been here a while
Local time
Today, 14:07
Joined
Jun 17, 2012
Messages
7,732
The code is, (I'll attached my Excel file, remember to change the path in the code):
Code: 
Function OpenformatSWP()
    Dim objexcel As Object
    Dim objworkbook As Object
    Dim CodeMod As Object
    Dim LineNum As Long
    Dim Code3 As String
    Dim destination2 As String
    
    destination2 = "C:\Access programmer\test.xls"
    Set objexcel = CreateObject("Excel.Application")
    objexcel.Visible = True
    objexcel.DisplayAlerts = False
    Set objworkbook = objexcel.Workbooks.Open(destination2)
    'Set CodeMod = objworkbook.VBProject.VBComponents("Sheet1").CodeModule
    Set CodeMod = objworkbook.VBProject.VBComponents("Ark1").CodeModule
    Code3 = ""
    Code3 = Code3 & " Dim lngLastRow" & vbNewLine
    Code3 = Code3 & " lngLastRow = Cells(Rows.Count, ""A"").End(xlUp).Row" & vbNewLine
    Code3 = Code3 & " Columns(""X:X"").Select" & vbNewLine
    Code3 = Code3 & " Selection.Insert Shift:=xlToRight" & vbNewLine & vbNewLine
    Code3 = Code3 & " Range(""X1"").Select" & vbNewLine
    Code3 = Code3 & " ActiveCell.FormulaR1C1 = ""common_id""" & vbNewLine & vbNewLine
    Code3 = Code3 & " Range(""X2"").Select" & vbNewLine
    With CodeMod
      LineNum = .CreateEventProc("Change", "Worksheet")
      '  .VBE.MainWindow.Visible = False
      LineNum = LineNum + 1
     .InsertLines LineNum, Code3
    End With
    objworkbook.Save
    objworkbook.Close
    'reopen Excel to run autostart
    objexcel.Workbooks.Open (destination2)
End Function
 

Attachments

  • Test.xls
    16.5 KB · Views: 98
K

Kobe2932

Registered User.
Local time
Today, 05:07
Joined
Nov 26, 2012
Messages
25
JHB said:
The code is, (I'll attached my Excel file, remember to change the path in the code):
Code: 
Function OpenformatSWP()
    Dim objexcel As Object
    Dim objworkbook As Object
    Dim CodeMod As Object
    Dim LineNum As Long
    Dim Code3 As String
    Dim destination2 As String
 
    destination2 = "C:\Access programmer\test.xls"
    Set objexcel = CreateObject("Excel.Application")
    objexcel.Visible = True
    objexcel.DisplayAlerts = False
    Set objworkbook = objexcel.Workbooks.Open(destination2)
    'Set CodeMod = objworkbook.VBProject.VBComponents("Sheet1").CodeModule
    Set CodeMod = objworkbook.VBProject.VBComponents("Ark1").CodeModule
    Code3 = ""
    Code3 = Code3 & " Dim lngLastRow" & vbNewLine
    Code3 = Code3 & " lngLastRow = Cells(Rows.Count, ""A"").End(xlUp).Row" & vbNewLine
    Code3 = Code3 & " Columns(""X:X"").Select" & vbNewLine
    Code3 = Code3 & " Selection.Insert Shift:=xlToRight" & vbNewLine & vbNewLine
    Code3 = Code3 & " Range(""X1"").Select" & vbNewLine
    Code3 = Code3 & " ActiveCell.FormulaR1C1 = ""common_id""" & vbNewLine & vbNewLine
    Code3 = Code3 & " Range(""X2"").Select" & vbNewLine
    With CodeMod
      LineNum = .CreateEventProc("Change", "Worksheet")
      '  .VBE.MainWindow.Visible = False
      LineNum = LineNum + 1
     .InsertLines LineNum, Code3
    End With
    objworkbook.Save
    objworkbook.Close
    'reopen Excel to run autostart
    objexcel.Workbooks.Open (destination2)
End Function
Click to expand...


Okay, I have absolutely no idea what's going on here. I use the same code and it gives me a totally different result, the macro always stops at this line, and in the excel file it inserts the code separately, not between Sub / End sub....just like before

LineNum = .CreateEventProc("Change", "Worksheet")

I have several worksheets in my file (4 pieces), and I want the macro to run only on Sheet1 (the sheet is calles INT), but it is properly referred in the below line:

Set CodeMod = objworkbook.VBProject.VBComponents("Sheet1").CodeModule
 
S

stallzy123

New member
Local time
Today, 13:07
Joined
Apr 16, 2013
Messages
9
Hi,

I'm only new to programming in access, but why is it that you don't just write the code in excel on the workbook and then call the macro to be run from access? Apologies if this is over simplified :)

Dim XL As Object
Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open Filename:="C:\Access programmer\test.xls", UpdateLinks:=0
XL.Run "Module1.Check"
ActiveWorkbook.Save
ActiveWindow.Close
 
K

Kobe2932

Registered User.
Local time
Today, 05:07
Joined
Nov 26, 2012
Messages
25
stallzy123 said:
Hi,

I'm only new to programming in access, but why is it that you don't just write the code in excel on the workbook and then call the macro to be run from access? Apologies if this is over simplified :)

Dim XL As Object
Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open Filename:="C:\Access programmer\test.xls", UpdateLinks:=0
XL.Run "Module1.Check"
ActiveWorkbook.Save
ActiveWindow.Close
Click to expand...

Hi

because it is a report with a standrad format and I have to update regurarly, as a new version of the report is published. I simply dont want to write / record / copy the macro every week as it is very time-consuming, but I want to automatize it, creating the code and running it from access.

what is strange to me that I have a similar macro (exports data from access and creates a report) with the very same syntax and it is working properly....
 
K

Kobe2932

Registered User.
Local time
Today, 05:07
Joined
Nov 26, 2012
Messages
25
Kobe2932 said:
Okay, I have absolutely no idea what's going on here. I use the same code and it gives me a totally different result, the macro always stops at this line, and in the excel file it inserts the code separately, not between Sub / End sub....just like before

LineNum = .CreateEventProc("Change", "Worksheet")

I have several worksheets in my file (4 pieces), and I want the macro to run only on Sheet1 (the sheet is calles INT), but it is properly referred in the below line:

Set CodeMod = objworkbook.VBProject.VBComponents("Sheet1").CodeModule
Click to expand...

finally I got some imporvements, the code properly appears in VBA on the proper Sheet (Sheet1):

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 Dim lngLastRow
 lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
 Columns("X:X").Select
 Selection.Insert Shift:=xlToRight
 Range("X1").Select
 ActiveCell.FormulaR1C1 = "common_id"
 Range("X2").Select

End Sub

the only problem that the sub does not run automatically. Am I missing from the above code ? or from the access code ? (shown above)
 
J

JHB

Have been here a while
Local time
Today, 14:07
Joined
Jun 17, 2012
Messages
7,732
When should it run, when/where do you insert something in Sheet1?
If you type in something on Sheet1 it runs, it wouldn't stop because ot the line;
ActiveCell.FormulaR1C1 = "common_id"
Click to expand...
.
 
K

Kobe2932

Registered User.
Local time
Today, 05:07
Joined
Nov 26, 2012
Messages
25
JHB said:
When should it run, when/where do you insert something in Sheet1?
If you type in something on Sheet1 it runs, it wouldn't stop because ot the line;
.
Click to expand...

I want to achieve the following steps with simply clicking on the command button in Access 2010.

1, Download the results of a query from access to excel, create the same common_id column in the file and save it as temporary.xls- that part is done, perfectly working

2, opening up a given excel file (in my code, it is test.xls), creating the same common_id field in it and using some vlookup to copy data from temporary.xls to test.xls - that's where I'm stucked.

Code3 section in my above code is the hal-finished code I want to run on test.xls, that will be further detailed, but the source of my problems is not Code3 at the moment.

What I want is having Code3 run in test.xls right after test.xls is opened by the access database.

(step 3 would be deleting temporary.xls)
 
K

Kobe2932

Registered User.
Local time
Today, 05:07
Joined
Nov 26, 2012
Messages
25
Kobe2932 said:
I want to achieve the following steps with simply clicking on the command button in Access 2010.

1, Download the results of a query from access to excel, create the same common_id column in the file and save it as temporary.xls- that part is done, perfectly working

2, opening up a given excel file (in my code, it is test.xls), creating the same common_id field in it and using some vlookup to copy data from temporary.xls to test.xls - that's where I'm stucked.

Code3 section in my above code is the hal-finished code I want to run on test.xls, that will be further detailed, but the source of my problems is not Code3 at the moment.

What I want is having Code3 run in test.xls right after test.xls is opened by the access database.

(step 3 would be deleting temporary.xls)
Click to expand...

bumping the thread.

Anyone can help me how to code step 2 I'm totally stucked with the previous codes. What I want to do is having a macro in access 2010 which opens up a given xls file and automatically inserts and runs a simple macro on the xls file (like Hello World, or something).

Do someone have a similar example for that, or sample code ?
 
You must log in or register to reply here.

Similar threads

M
Solved Access to Excel link
Replies
8
Views
777
bastanu
bastanu
W
CSV or XLSX Bank Statement to Access module (need to import via VBA and then write back to Excel with imported flag)
Replies
14
Views
1,173
Cotswold
Cotswold
Sam Summers
Inserting subform/Table data into specific fields in Excel
2
Replies
22
Views
1,597
Gasman
Gasman
B
Solved Method 'Cells' of object '_Global' failed in Excel session opened via Access
Replies
11
Views
1,128
Isaac
Isaac
J
Solved Error 1004 Unable to set the WindowState property of the Application class
Replies
9
Views
2,255
jocph
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom