I've googled to find out why, but maybe because I'm limited in English - so I can not find the Google result with an article similar like mine.
As I want to learn about ADO and data connection, so I'm playing around by making two workbooks : TABEL.xls and test.xlsm
TABEL.xls has a sheet name "Item" with two columns header in cell A1 "Item" and in cell B1 "Unit". There is one data in cell A2 and B2, say : "Fish" and "Kg". This workbook is close.
In test.xlsm, I have this kind of macro :
Sub test()
Dim conn As New Connection, rs As New Recordset
vl1 = "sapi"
vl2 = "Ons"
pf = "D:\Laporan DarmaPangan\TABEL.xls"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & pf & _
";Extended Properties=""Excel 8.0;HDR=yes;"""
sqlstr = "insert into [Item$](Item,Unit)" & _
"values ('" & vl1 & "', '" & vl2 & "')"
rs.Open sqlstr, conn, 1, 3
'rs.Close (throw me an error "it already closed")
conn.Close
End Sub
When I run the macro above, it didn't automatically open the TABEL.xls ... and when I open the TABEL.xls manually, I see the result where the item "sapi" is in cell A3 and unit "ons" is in cell B3. There's no problem here. I close back the TABEL.xls
Next, I'm playing around to try a connection from test.xlsm to TABEL.xls. So, within the test.xlsm : via Data tab in Excel ---> Existing Connection ---> Browse For More ---> I choose the TABEL.xls and choose the Item name, and choose Table view and Sheet1 cell A1 as the location.
So now in test.xlsm Sheet1, I have a table with the header "item" and "unit", also with a data "Fish" - "Kg" in cell A2 and B2 and also the newly inputted data "sapi" - "ons" in cell A3 and B3.
Now the thing which I thought in my mind is like this :
After I run the macro again with another item & unit value, the macro will put this data into TABEL.xls without opening the workbook. And when I click the Refresh All button, the table in Sheet1 will show the newly inputted data in cell A4 and B4.
But I was wrong, as it turn out : when I run the macro in test.xlsm, it automatically open TABEL.xls as read only. I see the newly inputted data in cell A4 and B4 in TABEL.xls .... but since this workbook is in read only mode, this new data is not saved yet which of course I can't just save it. So, even after clicking the Refresh button of course it didn't show an updated data table.
The next thing what I did :
I make a new workbook and have the same macro in this new workbook.
Close the test.xlsm and close the TABEL.xls.
Then I run the macro from this new workbook. This time, it didn't automatically open the TABEL.xls. Then I open the test.xlsm and after clicking the Refresh button, I see the new data in cell A4 and B4 within Sheet1.
I seems that if a workbook (in this case test.xlsm) has a table connection to another workbook (in this case TABEL.xls) and IF the macro like above is run within the same workbook, then the macro won't give an expected result as it will automatically open the TABEL.xls as read only.
So my question :
Is it actually my italic sentence a "default rule" which I should've known before and I shouldn't do it ? If yes, would somebody please explain it to me why is that ?
Or is it actually shoudn't happen like that ? So, maybe there's something which I should do ? A wrong macro code maybe ?
Any kind of help would be greatly appreciated.
Thank you in advanced.