Import Data with specifique sheet name (1 Viewer)

le888 · Jul 9, 2007

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

shades · Jul 9, 2007

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

le888 · Jul 9, 2007

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

shades · Jul 9, 2007

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

unmarkedhelicopter · Jul 10, 2007

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

le888 · Jul 10, 2007

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

Import Data with specifique sheet name (1 Viewer)

le888

Registered User.

shades

Registered User.

le888

Registered User.

shades

Registered User.

unmarkedhelicopter

Registered User.

le888

Registered User.

Similar threads

Users who are viewing this thread