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):
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
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
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