人に聞けない痔の悩み、これでスッキリ >>

VBA初心者でコード作成で困っております。

下記の通りコードを組みましたが、シート名をブック名に変更して
保存したいのですが、このコードですと拡張子までついてしまいます。
拡張子を除去するためにはどうすればよいでしょうか?

アドバイス宜しくお願い致します。

Sub test()

'シート名の変更

Dim MyPath As String

Dim MyFile As String

Dim Wb As Workbook


MyPath = "C:\TEST\"

MyFile = Dir(MyPath & "*.xlsx")


Do While MyFile <> ""

Set Wb = Workbooks.Open(MyPath & MyFile)

ActiveSheet.Name = ActiveWorkbook.Name

Application.DisplayAlerts = False

Wb.Save

Application.DisplayAlerts = True

Wb.Close (False)

MyFile = Dir()

Loop

End Sub

このQ&Aに関連する最新のQ&A

A 回答 (2件)

拡張子が .xlsx と決まっているなら



ActiveSheet.Name = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)

では?
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。

アドバイスして頂いたコードで無事出来ました。
とても参考になりました。

ありがとうございました。

お礼日時:2010/02/24 18:55

下記の方法でファイル名だけを取得出来ます。



tmp = "aaaac.xlsx"
MsgBox Left(tmp, Len(tmp) - 5)

----------------------------------------------
tmp = ActiveWorkbook.Name
ActiveSheet.Name = Left(tmp, Len(tmp) - 5)
    • good
    • 0
この回答へのお礼

早々のご回答ありがとうございます。

問題解決しました。
大変勉強になりました。

ありがとうございました。

お礼日時:2010/02/24 19:08

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QEXCEL VBA で現在開いているブックのファイル名を取得する方法

EXCEL2003 VBAで業務を簡素化するために、現在開いているブックのファイル名を取得する方法が分かりません。
作業手順をマクロを使って処理していますが、オリジナルのワークブックをファイル名を変えて保存し、以後、このワークブックを読み込んで使用しています。
このときのVBAは、オリジナルのファイル名を使っているため、ファイル名を変更するとエラーになり、以後の業務に使用できません。
常にファイル名を取得出来るVBAをどなたか、教えて下さい。

Aベストアンサー

>現在開いているブックのファイル名
 ちょっと曖昧な表現かなぁという気もいたしますが、VBAが書いてあるブックのブック名は
ThisWorkbook.Name
で、現在 "アクティブにして" 操作対象になっているブックの名前は
ActiveWorkbook.Name
ですね。

 しかし、
>VBAは、オリジナルのファイル名を使っているため、ファイル名を変更するとエラーになり
というような文脈からすると、
ThisWorkbook.Name
の方ですかね。

Q拡張子なしでファイル名を取得する

Windows7、EXCEL2013を使用しています。

VBAを使い、任意のフォルダを指定し、その中にあるファイル名を
・拡張子ありで取得するコマンドボタン(オブジェクト名:output)
・拡張子なしで取得するコマンドボタン(オブジェクト名:output_n)
の2パターンで取得するものをつくりたいと考えています。

拡張子ありのほうは、以下のように作成しました。
-----------------------------------------------------------
Private Sub output_Click()

Dim Target As String
Dim Extraction As Variant
Dim buf As String
Dim i As Long


'入力欄の値を取得

Target = Target_i.Value

'ファイル名取得
If Target = "" Then
MsgBox "パスを入力してください"
Exit Sub
End If

buf = Dir(Target & "*.*")

Do While buf <> ""
i = i + 1
Cells(i, 1) = buf
buf = Dir()
Loop

MsgBox i & "件ありました。"

End Sub
-----------------------------------------------------------
拡張子ありのこのコードは問題なく動作しています。
これを応用して拡張子なしに対応したものを作成したいのですが、
以下のようなコードにしたところ、エラーとなりました。
-----------------------------------------------------------
Private Sub output_n_Click()

Dim Target As String
Dim buf As String
Dim i As Long
Dim FileName As Object

Dim sFilename As String
Dim Findpoint As Long
Dim strLen As Long

'入力欄の値を取得
Target = Target_i.Value


'空白かチェック
If Target = "" Then
MsgBox "パスを入力してください"
Exit Sub
End If

'ファイル名取得
buf = Dir(Target & "*.*")

'拡張子をとる
sFilename = buf.getbasename(sFilename)   ←ここのbufが黄色で反転

Do While strPath <> ""
i = i + 1
Cells(i, 1) = strPath
strPath = Dir()
Loop

MsgBox i & "件ありました。"

End Sub
-----------------------------------------------------------
エラー内容は、
【コンパイルエラー:装飾子が不正です。】です。

一度ファイル名を取得し、拡張子をとった値を返したいのですが、
取得したファイル名(buf)から拡張子をとるにはどうすればよいでしょうか。

にわかの私にはこれ以上がわかりません。
どなたか教えてください。

宜しくお願いします。

Windows7、EXCEL2013を使用しています。

VBAを使い、任意のフォルダを指定し、その中にあるファイル名を
・拡張子ありで取得するコマンドボタン(オブジェクト名:output)
・拡張子なしで取得するコマンドボタン(オブジェクト名:output_n)
の2パターンで取得するものをつくりたいと考えています。

拡張子ありのほうは、以下のように作成しました。
-----------------------------------------------------------
Private Sub output_Click()

Dim Target As String
Dim Extraction As Variant
...続きを読む

Aベストアンサー

こんにちは。

ふつう、このような場合は、FileSystemObject (objFS) ではなく、InstrRev で、後ろから、コンマを探して、切り捨てます。

例:
Fn = "Test1.xlsx"
i = InStrRev(Fn, ".")
Debug.Print Left(Fn, i - 1)

なぜ、FileSystemObjectを使わないかというと、オブジェクトを呼びこむ時の時間、つまりオーバーヘッドが掛かるからです。今回は、ご質問者様の内容をそのまま活かしました。

'//
Private Sub output_n_Click()
 Dim Target   As String
 Dim Target_i   As Range
 Dim objFS   As Object 'FileSystemObject
 Dim buf    As String
 Dim i     As Long
 Dim FileName  As Object
 Dim strPath  As String
 Dim Findpoint As Long
 Dim strLen   As Long
 
 Set objFS = CreateObject("Scripting.FilesystemObject")
 
'入力欄の値を取得
 Set Target_i = ActiveCell '←※臨時で置きました。
 Target = Target_i.Value

'空白かチェック
If Target = "" Then
  MsgBox "パスを入力してください"
  Exit Sub
End If

'ファイル名取得
buf = Dir(Target & "*.*")
'拡張子をとる
 Do
 i = i + 1
 strPath = objFS.GetBaseName(buf) 'ループの中に入れました。
 Cells(i, 1).Value = strPath
 buf = Dir()
 Loop While buf <> "" '判定はbuf でします。
 
 MsgBox i & "件ありました。"
End Sub
'//

こんにちは。

ふつう、このような場合は、FileSystemObject (objFS) ではなく、InstrRev で、後ろから、コンマを探して、切り捨てます。

例:
Fn = "Test1.xlsx"
i = InStrRev(Fn, ".")
Debug.Print Left(Fn, i - 1)

なぜ、FileSystemObjectを使わないかというと、オブジェクトを呼びこむ時の時間、つまりオーバーヘッドが掛かるからです。今回は、ご質問者様の内容をそのまま活かしました。

'//
Private Sub output_n_Click()
 Dim Target   As String
 Dim Target_i   As Range
 Di...続きを読む

QEXCELファイルのカレントフォルダを取得するには?

EXCELファイルのカレントフォルダを取得するには?

C:\経理\予算.xls

D:\2005年度\予算.xls

EXCEL97ファイルがあります。

VBAで
  カレントフォルダ名
(C:\経理\,D:\2005年度\)
を取得する事は可能でしょうか?

CURDIRでは上手い方法が見つかりませんでした。

Aベストアンサー

こんばんは。
Excel97 でも、同じですね。以下で試してみてください。

Sub test()
'このブックのパス
a = ThisWorkbook.Path
'アクティブブックのパス
b = ActiveWorkbook.Path
'Excelで設定されたデフォルトパス
c = Application.DefaultFilePath
'カレントディレクトリ
d = CurDir
MsgBox "このブックのパス   : " & a & Chr(13) & _
   "アクティブブックのパス: " & b & Chr(13) & _
   "デフォルトパス    : " & c & Chr(13) & _
   "カレントディレクトリ : " & d & Chr(13)
End Sub

QExel VBA 別ブックから該当データを検索し、必要なデータを取得する方法について

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数ではなく、マクロで処理を希望します。

自分では、部品表の商品番号をコピーして、コード一覧表で検索し、検索結果の右隣のセル(B列のコード)の値を部品表のC列に貼り付ければよいかと思い、書いてみたんですが…

Sub 別ブックから貼り付ける()
  Dim 検索する As Long
Windows("部品表.xls").Activate
検索する = cells(i,2).Value
Windows("コード一覧表.xls").Activate
ActiveWindow.SmallScroll Down:=-3
Selection.AutoFilter Field:=3, Criteria1:="=検索する", Operator:= xlAnd

と、してみたものの、検索しても、その検索結果の隣のセルのコードをどうやって取得すればいいのかが、わかりませんでした。

基本事項は本で学びましたが、呪文のようなコードはよく理解できません。懸命にネットで検索して、訳して理解する努力をしてはいますが。

どうぞよろしくお願いします。

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数...続きを読む

Aベストアンサー

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks.Open("C:\★★\コード一覧表.xls") '★要変更★
 I = 2
 Do While Range("A" & I).Value <> ""
  ThisWorkbook.Worksheets("Sheet1").Range("C" & I).Value = Application.VLookup(ThisWorkbook.Worksheets("Sheet1").Range("B" & I).Value, xlBook.Worksheets("Sheet1").Range("A2:B65535"), 2, 0)
  I = I + 1
 Loop
 xlBook.Close
 Application.ScreenUpdating = True
 MsgBox ("完了")
End Sub

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks....続きを読む

QEXCEL VBAマクロ作成で、他のEXCELからデータを取り込みたい

メインプログラム(EXCEL VBA)より、
他のフォルダーにあるEXCELの項目の内容を取り込みたいです。
たとえば他のフォルダーのEXCELのRange("A2:A3").ValueをメインプログラムのRange("C2:C3").Valueにセットしたい時です。

・コマンドボタン押したら、どこのEXCELから取り込むかのポップアップ(?)は、表示はできてます。
・作業者が選んだパスとブックもMsgBoxで表示できてるので、もらう相手の場所も取得できてます。

・となると次はOPEN,INPUTですか?
テキストデータの取り込みですと、Inputでそのバッファを定義してるのですが、なんか違うような。。。

よろしくお願いします!

Aベストアンサー

私がやる方法です。

Dim writeSheet As Worksheet ' 自分自身の書き出し先シート
Set writeSheet = ThisWorkbook.Worksheets(1) ' Sheet1 を参照

Dim readBook As Workbook ' 相手ブック
Set readBook = Workbooks.Open(filename) ' 相手ブックを開いて参照
Dim readSheet As WorkSheet ' 相手シート
Set readSheet = readBook.Worksheets("sheetName") ' 相手シートを参照
' または Set readSheet = readBook.Worksheets(sheetIndex)

' 例えば
writeSheet.Cells(1, 1).Value = readSheet.Cells(2, 2).Value ' 相手シートの B2 の値を自分自身の A1 に書き込む

readBook.Close False ' 相手ブックを閉じる
Set readSheet = Nothing
Set readBook = Nothing

私がやる方法です。

Dim writeSheet As Worksheet ' 自分自身の書き出し先シート
Set writeSheet = ThisWorkbook.Worksheets(1) ' Sheet1 を参照

Dim readBook As Workbook ' 相手ブック
Set readBook = Workbooks.Open(filename) ' 相手ブックを開いて参照
Dim readSheet As WorkSheet ' 相手シート
Set readSheet = readBook.Worksheets("sheetName") ' 相手シートを参照
' または Set readSheet = readBook.Worksheets(sheetIndex)

' 例えば
writeSheet.Cells(1, 1).Value = readSheet.Ce...続きを読む

Qフォルダ内の全てのBookに同じ処理を繰り返す

フォルダ内にエクセルファイルが約3,000個あります。
この全てのBookに同じ処理をしたいのですが、マクロで繰り返す方法がわからないので教えて下さい。
処理をする内容は簡単なもので、マクロで作りました。

・ 各Bookには1つのシートしか存在せず、シート名は重要ではないので全て「Sheet1」になっています。
・ 各Bookのデータの配置や表形式は同じです。
・ レコードの行数がBookによって異なります。

処理の内容をマクロで作るところまではできましたが、知識がないためタイムアウトです。

ご教示宜しくお願い致します。

Aベストアンサー

だいたいこんな流れで。

sub macro1()
 dim myPath as string
 dim myFile as string

 mypath = "C:\test\"

’指定フォルダのブックを順繰り拾う
 myfile = dir(mypath & "*.xls*")
 do until myfile = ""

 ’ブックを開いて処理を行い保存して閉じる
  workbooks.open mypath & myfile
  activesheet.range("A1") = "DONE"
  activeworkbook.close true

  myfile = dir()
 loop
end sub


必要に応じて
・画面の表示を抑制する
・再計算を手動にする
といった手管を追加して高速化を図ります。

QVBAで自身のファイル名を取得する方法

Excelファイル自身が存在するディレクトリはCurDirで取得できました。しかし、Excelファイル自身のファイル名がある場所がわかりません。どうしたらよいでしょうか。

Aベストアンサー

ThisWorkbook.Name では

Qエクセル マクロで指定フォルダを開く

エクセルにて
指定フォルダを開く、マクロがあれば教えて頂けないでしょうか。
よろしくお願いいたします。

Aベストアンサー

こんにちは。

こういうものですか?
開くフォルダを変えたいときは targ に与えるパスを変更します。

Sub OpenFolders()
Dim targ As String
targ = "C:\"
Shell "C:\Windows\Explorer.exe " & targ, vbNormalFocus
End Sub

QEXCEL VBAで計算値を四捨五入、切り上げ、切捨てする方法

ネットで探してみたのですが、計算結果を四捨五入して特定のセルを
返すにはどうしたらいいのでしょうか?

Sub hokangosa()

Dim ZPS As Double
Dim ZPOS As Double
Dim DMN As Double
MsgBox (" >>> 補間誤差自動計算 <<< ")
MsgBox (" >>> 初期値入力します <<< ")
ZPS = InputBox(">>> ステップを入力してください<<<")
ZPOS = Sheet1.Cells(22, 4).Value
DMN = ZPOS / ZPS
Sheet1.Cells(23, 6).Value = DMN
End Sub

ここでDMNの値を四捨五入したいです。

またこれとは別に切上げ、切捨ても教えていただけるとありがたいです。

Aベストアンサー

DMN = Application.WorksheetFunction.Round(ZPOS / ZPS, 0)
で、四捨五入
DMN = Application.RoundDown(ZPOS / ZPS, 0)
で切り捨て
DMN = Application.RoundUp(ZPOS / ZPS, 0)
で切り上げです。

引数で、対象桁を変更できます。

Q別のシートから値を取得するとき

Worksheets("シート名").Activate
上記のを行ってから別シートの値を取得するのですが、
この処理を行うと指定したシートへ強制的にとんでしまいます。。。

※イメージ
For ~ To ~
  Worksheets("シートA").Activate
  シートAの値取得
       :
  Worksheets("シートB").Activate
  シートBの値取得
Next

このイメージ処理を行うとものすごい勢いで画面がチカチカします。。。
シートを変えずに他のシートから値を取得する方法はないのでしょうか。
教えてください!

Aベストアンサー

Worksheets("シートA").Range("A1")

みたいな感じでできませんか?


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング