Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-09-2007, 07:44 AM   #1
le888
Registered User
 
Join Date: Dec 2003
Location: Montreal, Canada
Posts: 344
Thanks: 0
Thanked 0 Times in 0 Posts
le888 is an unknown quantity at this point
Import Data with specifique sheet name

Hi,

I have written a code to import data and created a new Excel work sheet. Is it possible to name the sheet`s name instead of the default name (sheet1,sheet2,..)? Here is the part of the code:


Code:
Private Sub CmdImportData_Click()
'   Create new workbook with three sheets
    UserSheets = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = 4
    Workbooks.Add
    Application.SheetsInNewWorkbook = UserSheets
    
    Open ThisWorkbook.Path & "\matrice.out" For Input As #1
    r = 0
    c = 0
    CurrLine = 0
    Set ImpRange = ActiveWorkbook.Sheets(1).Range("A1")
    Application.ScreenUpdating = False


...
Thanks,

Le888

le888 is offline   Reply With Quote
Old 07-09-2007, 09:51 AM   #2
shades
Registered User
 
Join Date: Mar 2002
Location: Fly-over territory
Posts: 516
Thanks: 0
Thanked 1 Time in 1 Post
shades is on a distinguished road
Yes. Keep in mind that "name" of the worksheet may have two different values. Are you wanting to do this manually? In the Project Explorer window, you will see something like:

Sheet1(Sheet1)

Where the name inside the parentheses is the name of the tab (changed in Excel), but the the first name prior to the parentheses is underlying name Sheet1. You can change that name by looking in the Properties window (hit F4 while in VBE, and it will appear below the Project Explorer). The first itme is the name. In the second column you can double click and change the name "mySheet". Then the reference in the Project Explorer will look like this:

mySheet(Sheet1)

If you can the tab name in Excel to something else, then it will look like this:

mySheet(myTabName)

Is this what you were wanting?
________
Cr480

Last edited by shades; 02-16-2011 at 01:24 AM.
shades is offline   Reply With Quote
Old 07-09-2007, 09:58 AM   #3
le888
Registered User
 
Join Date: Dec 2003
Location: Montreal, Canada
Posts: 344
Thanks: 0
Thanked 0 Times in 0 Posts
le888 is an unknown quantity at this point
Yes, I want mysheet(myTabName). I would like that Excel do automately name the tab Name. For example, I would like my tab Name : first tab (vel_01), second tab (vel_02)...

Thanks,

Le888

le888 is offline   Reply With Quote
Old 07-09-2007, 01:34 PM   #4
shades
Registered User
 
Join Date: Mar 2002
Location: Fly-over territory
Posts: 516
Thanks: 0
Thanked 1 Time in 1 Post
shades is on a distinguished road
So, you are really wanting

Sheet1(vel_01)?

This is the line of code to rename the the tab name.

Code:
    Sheets("Sheet1").Name = "vel_02"
But, when you write "automatically", do you have these names in cells? Or are you sequencing them? Are you using any reference scheme?
________
Toyota motor engineering & manufacturing north america specifications

Last edited by shades; 02-16-2011 at 01:24 AM.
shades is offline   Reply With Quote
Old 07-09-2007, 11:58 PM   #5
unmarkedhelicopter
Registered User
 
unmarkedhelicopter's Avatar
 
Join Date: Apr 2007
Posts: 177
Thanks: 0
Thanked 0 Times in 0 Posts
unmarkedhelicopter is on a distinguished road
Adapt the following to your needs :-

Code:
sub renameshts
dim lnX as long
for lnx = 1 to worksheets.count
 if worksheets(1).name = "Sheet" & lnx then
  worksheets(1).name = "vel_" & Pad2(lnx)
 end if
next lnx
end sub

function Pad2(lnVal as long) as string
 if len(lnval) => 2 then
  Pad2 = right(lnval,2)
 else
  pad2 = "0" & lnval
 end if
end function
__________________
The Rotors have Stopped ! ... Is that a bad thing ?
2 + 2 = 9, My Arithmetic is Mental
unmarkedhelicopter is offline   Reply With Quote
Old 07-10-2007, 04:52 AM   #6
le888
Registered User
 
Join Date: Dec 2003
Location: Montreal, Canada
Posts: 344
Thanks: 0
Thanked 0 Times in 0 Posts
le888 is an unknown quantity at this point
Thanks, but where should I put the code? I have try to put after the "Application.SheetsInNewWorkbook = UserSheets". However, it did not also, I have try to put in different place. But, it don't works.

Thanks,

Le

le888 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Best way to import data on a monthly schedule DBDUDE Theory and practice of database design 1 08-17-2006 09:34 AM
Getting error in data base after using insert sql command in vba asingh Modules & VBA 5 07-15-2005 07:12 AM
error: couldn't use; file already in use DataMiner Queries 2 07-14-2005 01:10 PM
How to import data into similar DB, but unable to import the primary key? RSIboy Tables 4 08-25-2004 11:38 PM
data entry from data access sheet rascapillion General 2 09-27-2003 06:08 PM




All times are GMT -8. The time now is 03:40 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World