Modifying a linked Excel table from Access 365 (1 Viewer)

[Danick]

I've used the Linked Table manager to Link an Excel Table as the data source in Access. But the table needs a small change to one of the field titles. Very simple to just open the file in Excel and edit the field and then launch Access. But I'm trying to add a button in an Access form that will do that automatically.
In Excel, this can be done through VBA. Something like this

Sub vba_name_column()
Range("A1").value= "Completed"
End Sub

But can this same thing be done from an Access command button?

 

[isladogs]

See my article/video :

Edit Linked Excel Data from Access

This article describes two methods of making linked Excel files directly editable in Access

www.isladogs.co.uk

 

[CJ_London]

it can be done - you need to create an excel object, open the file through this object, use your code to change the header and save it

just google something like 'access vba edit an excel file' to find numerous examples such as this one

Redirecting

Or there is the sql method suggested by Colin so you have a linked query rather than a linked table. To modify the field name you just alias it. Using Colin's method 1 example

SELECT XL.*
FROM (SELECT * FROM [Sheet1$] AS xlData IN 'G:\MyFiles\ExampleDatabases\Excel\tblData.xlsx’
    [Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]) AS XL;

I don't know the column headers but as an example you might have

SELECT XL.*
FROM (SELECT hdr1 as newheader, hdr2 as anothernewheader, hdr3, hdr4... FROM [Sheet1$] AS xlData IN 'G:\MyFiles\ExampleDatabases\Excel\tblData.xlsx’
    [Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]) AS XL;

or

SELECT hdr1 as newheader, hdr2 as anothernewheader, hdr3, hdr4... 
FROM (SELECT * FROM [Sheet1$] AS xlData IN 'G:\MyFiles\ExampleDatabases\Excel\tblData.xlsx’
    [Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]) AS XL;

 

[Danick]

See my article/video :

Edit Linked Excel Data from Access

This article describes two methods of making linked Excel files directly editable in Access

www.isladogs.co.uk

Thanks isladogs - I'm using Access 365 and have been using method 2 to change the connection string from IMEX = 0 instead of the default value 2. So I can change the contents of the table, but can't find a way to add an additional column from Access.

Sorry for simplifying the original post, this is what I'm actually trying to.

Sub vba_add_column()
    Range("A1").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    Selection.Value = "Completed"
End Sub

This code will work inside Excel which adds a column after the last column and gives it a name. But a new data file saves to the same Excel file name, so the code is lost each time. So I'd like to try to do this from inside Access.

 

[Danick]

it can be done - you need to create an excel object, open the file through this object, use your code to change the header and save it

just google something like 'access vba edit an excel file' to find numerous examples such as this one

Redirecting

Or there is the sql method suggested by Colin so you have a linked query rather than a linked table. To modify the field name you just alias it. Using Colin's method 1 example

SELECT XL.*
FROM (SELECT * FROM [Sheet1$] AS xlData IN 'G:\MyFiles\ExampleDatabases\Excel\tblData.xlsx’
    [Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]) AS XL;

I don't know the column headers but as an example you might have

SELECT XL.*
FROM (SELECT hdr1 as newheader, hdr2 as anothernewheader, hdr3, hdr4... FROM [Sheet1$] AS xlData IN 'G:\MyFiles\ExampleDatabases\Excel\tblData.xlsx’
    [Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]) AS XL;

or

SELECT hdr1 as newheader, hdr2 as anothernewheader, hdr3, hdr4...
FROM (SELECT * FROM [Sheet1$] AS xlData IN 'G:\MyFiles\ExampleDatabases\Excel\tblData.xlsx’
    [Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]) AS XL;

Thanks for the suggestion CJ. I may try this method if I can't get it working directly from within Access. Otherwise, I'll just keep updating the Excel file with Excel before launching Access.

 

[arnelgp]

since you are Writing values to Row 1, therefore your excel sheet has no Header?
what i would do is use Excel automation to accomplished the task:

Sub vba_add_column()
    'Put the name of your Linked Excel sheet here
    Const LINKED_EXCEL As String = "Sheet1"
    Const xlToLeft As Integer = -4159
    Dim oExcel As Object
    Dim oWB As Object
    Dim workbook_name As String
    ' extract the workbook name and path
    workbook_name = CurrentDb.TableDefs(LINKED_EXCEL).Connect
    workbook_name = Mid$(workbook_name, InStrRev(workbook_name, "=") + 1)
    
    ' Better Check if the workbook is already open
    ' if it is, cancel this routine
    If IsFileOpen(workbook_name) Then
        MsgBox "The workbook is already open. Please close the workbook first and try again."
        Exit Sub
    End If
    
    ' create excel instance
    Set oExcel = CreateObject("Excel.Application")
    
On Error GoTo Err_Handler:
    ' open the workbook
    Set oWB = oExcel.workbooks.Open(workbook_name)
    ' i am supposing that Sheet1 is the target sheet
    With oWB.Sheets(1)
        With .Cells(1, .Columns.Count).end(xlToLeft)
            .Offset(, 1) = "Completed"
        End With
    End With
    oWB.Close True
Exit_Sub:
    Set oWB = Nothing
    oExcel.Quit
    Set oExcel = Nothing
    Exit Sub
Err_Handler:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_Sub
End Sub

' https://exceloffthegrid.com/vba-find-file-already-open/
Function IsFileOpen(fileName As String)
    Dim fileNum As Integer
    Dim errNum As Integer
    'Allow all errors to happen
On Error Resume Next
    fileNum = FreeFile()
    'Try to open and close the file for input.
    'Errors mean the file is already open
    Open fileName For Input Lock Read As #fileNum
    Close fileNum
    'Get the error number
    errNum = Err
    'Do not allow errors to happen
On Error GoTo 0
    'Check the Error Number
    Select Case errNum
        'errNum = 0 means no errors, therefore file closed
        Case 0
        IsFileOpen = False
         'errNum = 70 means the file is already open
        Case 70
        IsFileOpen = True
        'Something else went wrong
        Case Else
        IsFileOpen = errNum
    End Select
End Function

 

[Danick]

since you are Writing values to Row 1, therefore your excel sheet has no Header?
what i would do is use Excel automation to accomplished the task:

Sub vba_add_column()
    'Put the name of your Linked Excel sheet here
    Const LINKED_EXCEL As String = "Sheet1"
    Const xlToLeft As Integer = -4159
    Dim oExcel As Object
    Dim oWB As Object
    Dim workbook_name As String
    ' extract the workbook name and path
    workbook_name = CurrentDb.TableDefs(LINKED_EXCEL).Connect
    workbook_name = Mid$(workbook_name, InStrRev(workbook_name, "=") + 1)
   
    ' Better Check if the workbook is already open
    ' if it is, cancel this routine
    If IsFileOpen(workbook_name) Then
        MsgBox "The workbook is already open. Please close the workbook first and try again."
        Exit Sub
    End If
   
    ' create excel instance
    Set oExcel = CreateObject("Excel.Application")
   
On Error GoTo Err_Handler:
    ' open the workbook
    Set oWB = oExcel.workbooks.Open(workbook_name)
    ' i am supposing that Sheet1 is the target sheet
    With oWB.Sheets(1)
        With .Cells(1, .Columns.Count).end(xlToLeft)
            .Offset(, 1) = "Completed"
        End With
    End With
    oWB.Close True
Exit_Sub:
    Set oWB = Nothing
    oExcel.Quit
    Set oExcel = Nothing
    Exit Sub
Err_Handler:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_Sub
End Sub

' https://exceloffthegrid.com/vba-find-file-already-open/
Function IsFileOpen(fileName As String)
    Dim fileNum As Integer
    Dim errNum As Integer
    'Allow all errors to happen
On Error Resume Next
    fileNum = FreeFile()
    'Try to open and close the file for input.
    'Errors mean the file is already open
    Open fileName For Input Lock Read As #fileNum
    Close fileNum
    'Get the error number
    errNum = Err
    'Do not allow errors to happen
On Error GoTo 0
    'Check the Error Number
    Select Case errNum
        'errNum = 0 means no errors, therefore file closed
        Case 0
        IsFileOpen = False
         'errNum = 70 means the file is already open
        Case 70
        IsFileOpen = True
        'Something else went wrong
        Case Else
        IsFileOpen = errNum
    End Select
End Function

Thanks arnelgp!!

It's working but not quite. Something isn't quite right.

If I run the code as is, I can't get passed the "The workbook is already open..." message box. Suppose that's expected since I need to have the workbook as a linked table in Access. If I remove that step, the code runs and gets to the "Save As" dialog. It won't let me save it as the current workbook name, so I gave it another name just to test the output. Basically, the code does work with a new properly named column, but now I have some cleanup to do outside of Access again.

Any ideas?

​

 

[CJ_London]

may try this method if I can't get it working directly from within Access.

You can use the query method to change a field name per the original request, but not add a new column per post #4. The query method will only return columns with data so consequently you can't change a heading which doesn't exist

 

[Gasman]

Put dummy columns in your workbook?, then amend as needed.?

 

[CJ_London]

No that won't work - you would still need to alias

if you set HDR=no;IMEX=0;

you still get the headers

changing IMEX to 2 will put the headers in the first row of data - but of course you can't edit it.

 

[Danick]

Put dummy columns in your workbook?, then amend as needed.?

The Excel spreadsheet is a system generated file which I have no control over. The idea is to link to the raw file that gets replaced by the system, modify as needed with Access and then export it another excel file for distribution. For reasons too many to mention here, it's a lot easier to view and manipulate the data table using Access forms, subforms, queries, etc.. rather than with Excel.

 

[Gasman]

The Excel spreadsheet is a system generated file which I have no control over. The idea is to link to the raw file that gets replaced by the system, modify as needed with Access and then export it another excel file for distribution. For reasons too many to mention here, it's a lot easier to view and manipulate the data table using Access forms, subforms, queries, etc.. rather than with Excel.

Ok, so I would then open the excel file with VBA, and add the relevant columns etc.
Then link.?

However reading your first post, why not just create a query from that table and amend the errant column name as you do so.

Notwanted_column_name AS Wanted_Column_Name

and process the query instead.?
However it then changed to ADD a column name?, which again could be done with a query?

SELECT Transactions.*, "" AS Completed
FROM Transactions;

Is that a possibility?

As you are attempting to add a column, there cannot be any data in it, surely?, else it would have a header name?

 

[Danick]

Ok, so I would then open the excel file with VBA, and add the relevant columns etc.
Then link.?

Yes that's what I'm doing now. Works fine but wanted to try and automate it all with Access.

As you are attempting to add a column, there cannot be any data in it, surely?, else it would have a header name?

Yes, I am attempting to add a column that has no header name, give it a name and then populate that new column from within Access. I can do all of this except add the new column from Access.

I'm thinking the only way around would be to create an Excel file with one button that will run the VBA to make changes to other Excel File, and then launch the Access Database that links to that Excel Table. Seems kind of a silly way to get automation...

 

[Gasman]

Yes that's what I'm doing now. Works fine but wanted to try and automate it all with Access.


Yes, I am attempting to add a column that has no header name, give it a name and then populate that new column from within Access. I can do all of this except add the new column from Access.

I'm thinking the only way around would be to create an Excel file with one button that will run the VBA to make changes to other Excel File, and then launch the Access Database that links to that Excel Table. Seems kind of a silly way to get automation...

Notice I said VBA to open the excel workbook?, not the GUI? So all within Access

Not sure if you could do that with it linked, but you could try at least?
Else a query getting data from the Excel sheet and populating a table, would do that?

 

[Danick]

Notice I said VBA to open the excel workbook?, not the GUI? So all within Access

I can't modify the Excel Table within Access. It's a linked table, so Access will want it closed before it can modify the structure. But since it's linked, I can't close it either.

 

[Gasman]

So a query to a temp table then?
You could also of course delink, edit and relink? Not something I have ever done, but it is possible?

From https://www.google.com/search?q=link+excel+workbook+acces+vba&sxsrf=ALiCzsa3o7Yt3pmHs_JPa2K6kQ5Blc76kQ:1666904252787&ei=vPBaY9jLL-mbhbIP2YyS4A8&ved=0ahUKEwjYmfKepoH7AhXpTUEAHVmGBPwQ4dUDCA8&uact=5&oq=link+excel+workbook+acces+vba&gs_lp=ugYGCAEQARgJEgdnd3Mtd2l6uAED-AEBMgoQIRjDBBgKGKABwgIKEAAYRxjWBBiwA8ICDhAAGOQCGNYEGLAD2AEBwgIHECMYsAIYJ8ICCBAAGAgYBxgewgIFEAAYhgPCAggQABgIGB4YDZAGDUj5GFCAB1j9D3ABeADIAQCQAQCYAYIBoAHiBKoBAzMuM-IDBCBNGAHiAwQgQRgA4gMEIEYYAYgGAQ&sclient=gws-wiz

One of many

VBA to link Excel Sheets to Access

social.msdn.microsoft.com

 

[Pat Hartman]

As long as you don't actually have the linked table open in Access, you can manipulate the file using VBA and OLE without a problem. Access will refresh the link when you open the table so it should see your update.

I had a problem with an excel file that I got from a third party. One of the columns had a leading space. Do you have any idea how unhappy Access is when you try to open a linked table with a column that has a leading space? You don't want to know. Since the third party could care less about how unhappy Access was since most other clients were using excel to open the file and that didn't care, I was on my own.

arne gave you code. Try that.

 

[Danick]

since you are Writing values to Row 1, therefore your excel sheet has no Header?
what i would do is use Excel automation to accomplished the task:

Sub vba_add_column()
    'Put the name of your Linked Excel sheet here
    Const LINKED_EXCEL As String = "Sheet1"
    Const xlToLeft As Integer = -4159
    Dim oExcel As Object
    Dim oWB As Object
    Dim workbook_name As String
    ' extract the workbook name and path
    workbook_name = CurrentDb.TableDefs(LINKED_EXCEL).Connect
    workbook_name = Mid$(workbook_name, InStrRev(workbook_name, "=") + 1)
 
    ' Better Check if the workbook is already open
    ' if it is, cancel this routine
    If IsFileOpen(workbook_name) Then
        MsgBox "The workbook is already open. Please close the workbook first and try again."
        Exit Sub
    End If
 
    ' create excel instance
    Set oExcel = CreateObject("Excel.Application")
 
On Error GoTo Err_Handler:
    ' open the workbook
    Set oWB = oExcel.workbooks.Open(workbook_name)
    ' i am supposing that Sheet1 is the target sheet
    With oWB.Sheets(1)
        With .Cells(1, .Columns.Count).end(xlToLeft)
            .Offset(, 1) = "Completed"
        End With
    End With
    oWB.Close True
Exit_Sub:
    Set oWB = Nothing
    oExcel.Quit
    Set oExcel = Nothing
    Exit Sub
Err_Handler:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_Sub
End Sub

' https://exceloffthegrid.com/vba-find-file-already-open/
Function IsFileOpen(fileName As String)
    Dim fileNum As Integer
    Dim errNum As Integer
    'Allow all errors to happen
On Error Resume Next
    fileNum = FreeFile()
    'Try to open and close the file for input.
    'Errors mean the file is already open
    Open fileName For Input Lock Read As #fileNum
    Close fileNum
    'Get the error number
    errNum = Err
    'Do not allow errors to happen
On Error GoTo 0
    'Check the Error Number
    Select Case errNum
        'errNum = 0 means no errors, therefore file closed
        Case 0
        IsFileOpen = False
         'errNum = 70 means the file is already open
        Case 70
        IsFileOpen = True
        'Something else went wrong
        Case Else
        IsFileOpen = errNum
    End Select
End Function

Hello arne

After Pat's comments about being able to modify the table if the table was closed, I decided to give your code another shot. This time, I created a new form with just one button and this VBA. To my surprise, it worked. The reason being that I had put this code in an existing form which still had a query with hooks in the table. So basically, the table was still open when trying to run your code. Now it works flawlessly and am able to add a column and prepare the spreadsheet for data input all from within Access.

Thanks for your help and everyone who contributed. I keep coming back here to this forum for solutions and you guys have never let me down.

 

[Pat Hartman]

I manipulate the columns names in the procedure I use to link to the table. In my case, the file always has a different name. If yours always has the same name, you should probably open the file in your startup process to see if it needs fixing before you get into your normal stuff.

 

[Danick]

I manipulate the columns names in the procedure I use to link to the table. In my case, the file always has a different name. If yours always has the same name, you should probably open the file in your startup process to see if it needs fixing before you get into your normal stuff.

Yes that would be a much better way of doing it. Right now, I'm launching Access and assume that I can get into the normal stuff. But if not, then I get a "Enter Parameter Value" dialog with a following Run-time error '2467". So I just click "End" and then click a button I called "Prepare Table for Data entry". This basically closes all forms with links to the table, runs anre's code and finishes with a msgbox "Your table has been prepared for data entry" This gets the job done, but I agree with you that it would be more professional to check the table first and run all this stuff without any error messages.