Topics Search

change linked sheet name

change linked sheet name
Views: 33
I have Access programs that are linked to Excel spreadsheets. Due to circumstances beyond my control the sheet names have been changed and I will have to use the new names in my programs.

I have tried to go into Link Manager but it does not let me change the sheet name only the file name.

Does anyone know an easy way to change the Sheet name in a linked Excel spreadsheet without messing up the connections in queries etc.
Sponsored Links:

Similar posts...

 

Create a linked table from excel using VBA

Create a linked table from excel using VBA Icon
I have 10 different sheets in a workbook and I want to make link the two sheets to my database which are sheet 1 and 10. In linking the sheet 1 I am using this code:

DoCmd.TransferSpreadsheet acLink, , "Sheet1", "Workbook1", True

by default, is getting the first sheet of the workbook.

Is there any way to put the Sheet name to be linked? My problem is how to link the Sheet 10 using vba.
 

Linking Excel Into Access Issue

Linking Excel Into Access Issue Icon
I am using Acc 2003, but Acc 2007 should work the same (I think).

I have linked an excel spreadsheet into my Access database. Almost all of the fields are available and can be read.

There are a few fields that I can see in Access, but its in Excel sheet. These fields were using a VLookUp function in excel to get the value from another excel worksheet.

Also the linked excel sheet has fields that I believe should be text (in Access), but its was set to be a number. I am unable to change this.

Is there a way to resolve this issue for me ?

I also assume that if I was to read each cell in the excel sheet and convert it into text files using VBA code, that should resolve the issue.
 

convert linked table

convert linked table Icon
I have linked an excel sheet to access
From within access how can I convert the linked table to a regular access table.
Microsoft shows how for access 2003 but not for 2002

The excel sheet has 92 headers on one row and that is all I'm trying to export to Access but I am unable to do it by importing external data from within access . the import fails . allheaders in excel are formated "text"
 

Linking cells with date format to Access

Linking cells with date format to Access Icon
I have an Excel sheet linked to Access 2007. This way when I update the Excel sheet, the queries in Access update automatically.

I have a problem with a field with date format in the Excel sheet. The query result in Access linked to this sheet, shows the value of the cell in numbers, that is the numbers that represent the date.

In Excel for eg. 23/02/2006 is equivalent to 37309. My Access query shows 37309.

What can I do so that my Access database shows the field in date format?
 

Changing the connection information on a linked QUERY in Access 2007

Changing the connection information on a linked QUERY in Access 2007 Icon
This is about linked queries, not linked tables. With no linked tables, the Linked Table Manager is disabled.

I know how to see the connection of a linked query, linked to SQL Server: ?CurrentDB.QueryDefs("create").Connect
"ODBC;DRIVER=SQL Server;
SERVER=PRODSERVER;DATABASE=proddatabase;Trusted_Co nnection=Yes"
I need to change the server name in the connection.

I tried in the Immediate window
CurrentDB.QueryDefs("create").Connect = ".test server name."
but that did not change the connect property.

How do I change the connect property?
 

Change connection string of linked table

Change connection string of linked table Icon
I want to change the filepath of a linked table in VBA.
So far I've got this:-

Sub test()
Dim tbl As TableDef
For Each tbl In TableDefs
If Len(tbl.Connect) > 0 Then
...
End Sub

As far as I can make out, checking the Connect property tells me if it's a linked table, but I'm not sure how to change just the path part.
 

Access(ing) Excel data (pun intended)

Access(ing) Excel data (pun intended) Icon
I have a data acquisition program that stores plant data in real time, and daily totals in Excel. I have built a workbook that complies this data, and is linked to a report sheet that does the calculations.

Some of the data for the reports must be manually entered into the cells, as they are not in the DAS.

I am in the process of moving the stored data to an Access database. In the process, I want to automate the reporting process. I have a Excel sheet that linked to the cells needed in the DAS Excel sheet, and to the manually entered values.
Ex.
GROSSMW AUXMW FUEL STEAM
124, 7, 28, 768 I know the columns did not look right in this ex. but they are correct in the Excel file.

In this example, the GROSSMW and AUXMW are manually entered into a cell, and the FUEL and STEAM are linked to the DAS Excel sheet and transfer the values automatically into these cells.

What I want to do is, have the user go into Access on a form and have the DAS values populate their respective fields, Then be able to click a command button to save the form data to the access database.

I already have the Excel sheets built that will get the requested data from Access and populate the reports. I am justunsure how to make Access and Excel communicate like I want them to.
 

Record the change and playback them as a video

Record the change and playback them as a video Icon
I have one form which I need to record the change that may happen to this Form for the last 10 minute (this form was created by VBA code and take his information from an Excel sheet,this form has different values which go up and down based on the value we entered in the excel sheet), and I would like to be able to playback this change in a video.

I need to record all of these changes and be able to go back and see all this change for the last 10 minute as it happened now by pressing a command button called playback.

I mean it is like someone videoing your action for the last 10 minute and play them back.
 

Import a specific excel sheet from list of sheets in workbook using Access VBA

Import a specific excel sheet from list of sheets in workbook using Access VBA Icon
I'm importing data from Excel to Access using Access VBA which works fine that sheet name is first sheet in workbook. However, if multiple sheets exists in the workbook and that particular sheet can be 2 sheet, 3 sheet or any sheet.

In that case how to search particular sheet name and import in access. Below is the code I have used for importing the data.
 

Access DSN Mass Change

Access DSN Mass Change Icon
The link table manager is prompting me for each and every of the thousands of tables linked to the DSN. We are working on upgrading sql and we have access databases with linked tables. I need to change the DSN on every linked table all at once.