Solved

How do you change the Name of a table when you change to a excel table?

Posted on 2013-10-23
1
376 Views
Last Modified: 2013-10-23
I am transferring a table to excel format and then it goes to a folder on the c drive.
How do I automatically change the name of the excel spreadsheet once it is converted to excel?

Dim strTable1Name1        As String
Dim strBasePath             As String

strBasePath = "C:\InductionImport\"
strTable1Name1 = strBasePath & "tblFS3ResponsesSelected.xlsx"

DoCmd.TransferSpreadsheet acExport, , "tblFS3ResponsesSelected", strTable1Name1
MsgBox "Export Complete, Data is Now in InductionImport Folder!"

DoCmd.OpenQuery "qryDeletetblFS3ResponsesSelectedFromMain", acViewNormal, acEdit
MsgBox "Records Have Been deleted from Main table in Database!"

DoCmd.OpenQuery "qryDeletetblFS3ResponsesSelected", acViewNormal, acEdit
MsgBox "Records Have Been deleted from tblFS3ResponsesSelected!"
MsgBox "Select Record to Export !"
0
Comment
Question by:gigifarrow
1 Comment
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39595037
you can change the name from this string

strTable1Name1 = strBasePath & "tblFS3ResponsesSelected.xlsx"

example

strTable1Name1 = strBasePath & "NewExcelName.xlsx"


or

strBasePath = "C:\InductionImport\"
strTable1Name1 = strBasePath & "tblFS3ResponsesSelected.xlsx"

after the export commands


if dir(strTable1Name1)<>"" then

name strTable1Name1 as strBasePath & "Newname.xlsx"

end if
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now