それぞれテキストファイに区切って出力

質問

添付画像のような構成で
　C列数値（D列の文字数の総計）を上から順番に合計して300を超えるとB列にその合計を書き出すようにしました。
　（コード SumIfOver300)

わかりやすいようにA列にそれぞれを塗分けしています。

1) 現在は、手動ですがこれをVBAで自動で処理したい。
（添付図は、３色ですが境目が解ればいいので2色でもOKです。）

2) 塗分け後に塗分けされたD列の部分を一つのテキスト群としてそれぞれ別ファイル（テキストファイル）に区切って出力したい。

添付図で言うといかのように出力
　D2：D7　 001_text.txt
D8:D12 002_text.txt
D13:D15 003_text.txt

Sub SumIfOver300()
Dim lastRow As Long
Dim sum As Long
Dim i As Long

lastRow = Cells(Rows.Count, "C").End(xlUp).Row
sum = 0

For i = 1 To lastRow
sum = sum + Cells(i, "C").Value
If sum > 300 Then
Cells(i, "B").Value = sum
sum = 0
End If
Next i
End Sub

回答No.28

kkkkkm · ベストアンサー

> 　</i>は、
> 　　fileContent = Replace(fileContent, "/", "%EF%BC%8F")
> 　　実際は、< / i>に先に変換されているので無駄なコードになっているようです。

上記を
fileContent = Replace(fileContent, "</i>","")
の後に実行すればいいのではないですか。
置換の順番は、文字数の多いものから先にするといいのではないでしょうか。
ただ
「%」は必ず一番最初に置換
は、あのグループの中で他の置換の結果に「%」が含まれるから、それを置換しないようにするためです。

> 事前にReplaceで削除しておきたいのですが
> どのようなコードになりますか　？

fileContent = Replace(fileContent, "</i>","")
と同じようにすればいいと思います。

お礼

ありがとうございます。

難しく考えていました。
アドバイスのように削除記号をひとつずつコードで追加すれば良いのですね。

（　<*>　のようにワイルドカード的な処理を想定していました。）

以下のコードに変更してしばらくテストしてみます。

fileContent = Replace(fileContent, "%", "%25")

fileContent = Replace(fileContent, "<i>", "")
fileContent = Replace(fileContent, "</i>", "")

fileContent = Replace(fileContent, "<b>", "")
fileContent = Replace(fileContent, "</b>", "")

fileContent = Replace(fileContent, "<BR>", "")

fileContent = Replace(fileContent, "#", "%23")
fileContent = Replace(fileContent, "/", "%EF%BC%8F")

pStr = Replace(Replace(Replace(fileContent, vbCr, "%0D"), vbLf, "%0A"), " ", "%20")

’---------------------------------------------

回答No.17

kkkkkm

凄く単純な方法ですが、VBAで以下のようにするとDeepLで結果が出せます。
Omisoも最終結果はコピペだと思いますから、毎回コピペにはなりますが手動でやるよりは楽だと思います。

以下は3回のテスト版です。A1からA3まで英文を改行無しで入れておいて実行します。
一回ごとに「次の翻訳」が出るので大量にDeepLが出るのを防げます。

Sub Test()
Dim i As Long
Dim WSH
Set WSH = CreateObject("Wscript.Shell")

For i = 1 To 3
WSH.Run "https://www.deepl.com/translator#en/ja/" & Replace(Cells(i, "A"), " ", "%20")
If MsgBox("次の翻訳", vbOKCancel) = vbCancel Then
Exit For
End If
Next
Set WSH = Nothing

End Sub

回答No.16

kkkkkm

> 「このサイトにアクセスできません。
> localhostで接続が拒否されました。」とエラー表示されてしまいます。

batを実行したままだと先に記載したようになると思います。

> 「Omiso翻訳」アイコンをクリックして数秒後にGoogleのポップアップウインドウに
> 問題の上限表示が出ると参考画像の①のような水色のアイコンがでます。

はい、長文にしたら出るようになりました。ポップアップが出るのは遅いですが、他は同じです。

ちなみに、元の紹介ページの最後の方に記載されていた以下を試すとちゃんと実行されました。

>> https://www.deepl.com/translator#en/ja/This%20is%20a%20pen.を開くことで、DeepLによる翻訳を実行することができる。

実際
https://www.deepl.com/translator#en/ja/
の後ろに、長文でも英文を一行にしてスペースを%20に変換し、それを継ぎ足したリンクを実行するとDeepLが開いて翻訳してくれます。

多分、上記の方法をOmisoServer.ps1で行っているのだけど、最初の方にある$contextに値が入ってないみたいなので、受け渡しがうまくできていないのではないかと思います。

お礼

kkkkkmさん、No.15,No16において検証いただきありがとうございます。

DeepLの制限問題ではなくOmisoのスクリプトの問題で
エラー表示等も私と同じ状況だと言う事がはっきりしました。

これ以上のスキルのない私の方でチェックできる事はなさそうなので
omisoの利用は諦めます。

長々とお付き合い頂きありがとうございます。

回答No.15

kkkkkm

同じエラーが出ました。
私が作った英文の一文が短すぎたのかもしれません。長い文章にしたらポップアップもエラーも同じ状態になりました。

DeepLでは上限に達していないことは確認しましたので、このスクリプトに問題がある(もしくは何かしらのセキュリティ)のかもしれません。

回答No.14

kkkkkm

> 数秒後にGoogleにポップアップウインドウが出現して
> 　「このページの内容
> 　　　　DeepLの翻訳上限に達した可能性があります。」
> と表示される。

これは、DeepLからの応答がない時に「Omiso翻訳」アイコンのjavascriptが出しているメッセージだと思いますので、実際上限に達したかどうかは分からないと思います。

> cmd画面に新しく
> 　「null　配列にインデックスを付けることはできません。

OmisoServer.ps1の最初の方にあるlocalhostとかのURLをクリックすると
callback({result: "", translationId: 0});
とchromeの新しいタブに表示され、PowerShellの方では同じエラーメッセージが出ますが、翻訳アイコンをクリックしたときには、ポップアップもchromeの表示もエラーも起きません。

「localhostにリクエストを出していないような感じ」
は
OmisoServer.ps1の

# requestが来たら翻訳処理をJobとして実行し、jobArrayに追加
if(($task.IsCompleted) -And ($jobArray.Count -lt $maxJob)){

以降先に進まない状態です。「翻訳中」も出ないのでjavascriptがうまく動いてないのかもしれません。

NuboChanさんの場合は、メッセージが出たりエラーが出たりなので、私の実行時と同じ状況ではないと思います。

補足

話の内容がだんだん難しくなってき付いて行けていない状況です。

>OmisoServer.ps1の最初の方にあるlocalhostとかのURLをクリックすると
>callback({result: "", translationId: 0});
>とchromeの新しいタブに表示され、

「OmisoServer.ps1の最初の方にあるlocalhostとかのURL」とは、
http://localhost:8000/?　の事だと思いますが
私の環境では、このURLをクリックしても
「このサイトにアクセスできません。
localhostで接続が拒否されました。」とエラー表示されてしまいます。

http://127.0.0.1/Temporary_Listen_Addresses/　をクリックしても同じエラーが出ます。

>DeepLからの応答がない時に「Omiso翻訳」アイコンのjavascriptが出しているメッセージだと思いますので、
>実際上限に達したかどうかは分からないと思います。

なるほど、DeepLの上限問題とは関係ない可能性もあるのですね。

>「翻訳中」も出ないので

関係ないと思いますが、状況説明ですが、

「Omiso翻訳」アイコンをクリックして数秒後にGoogleのポップアップウインドウに
問題の上限表示が出ると参考画像の①のような水色のアイコンがでます。
ポップアップウインドウで「OK」すると
水色のアイコンがクリックできるようになり
クリックすると①の画面が消えて②のように「翻訳中」が表示されます。
但し、「翻訳中」は表示だけのようで実際は何も処理されていないのか？
以後は何の変化もなりません。　（更なるエラー表示なども無い）

>NuboChanさんの場合は、メッセージが出たりエラーが出たりなので、
>私の実行時と同じ状況ではないと思います。

11:50に記載した状況（メッセージ及びエラー表示）が毎回出るので
kkkkkmさんと私の状況は、やはり違うようですね。

参考画像
https://imgur.com/JWiguv9

回答No.13

kkkkkm

> その後、手順を「あーでもない、こうかな、これではどうか、それではこれで」的に色々模索しましたが
> 結果的には翻訳が出来ませんでした。

残念ですね。私も試してみましたが、やり方が悪いのだと思いますが、ブックマークのリンクのやつクリックしても、localhostにリクエストを出していないような感じで無反応でした。

お礼

試しにダメ元でChromeのシークレットモードで試してみましたが、同じ状況です。

kkkkkmさんの言及されている「localhostにリクエストを出していないような感じ」とは、
私の場合で言うと、以下概略にあるcmd画面の事だと思います。

同じ状況ならomisoの使用は、DeepLの上限に無関係なので諦めます。

’------------------------------------

Omisoのbat経由(起動)してPs1を起動。
　cmd画面に「Translation Gummy Omiso Server is runnning....」と表示された状態のまま

Googleで新しいシークレットモードを起動
翻訳すべきhtmlファイルをシークレット画面にD＆D
　　Google画面に翻訳すべきテキストが表示される
Googleに登録済みの「Omiso翻訳」アイコンをクリック

数秒後にGoogleにポップアップウインドウが出現して
　「このページの内容
　　　　DeepLの翻訳上限に達した可能性があります。」
と表示される。

この時,
cmd画面に新しく
　「null　配列にインデックスを付けることはできません。
　　　+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualiedErrorId : NullAray
+ PSComputerName : localhost

と表示されます。
　　（ポップアップウインドウとどちらが先に表示されるのかは瞬時なので不明）

回答No.12

kkkkkm

「セキュリティ的にダウンロードができない場合」は、ファイルのダウンロードが許可されていないなどでZIPファイルがダウンロードできなければ、中身をリンク先からコピペしてファイルを自分で作ってねという事だと思います。

> 以下の使用方法は、翻訳したいテキストファイルをHTMLに変換する必要があるとの
> kkkkkmさんのアドバイスがあるので実行まで至っていません。

「仕組み」のところで

>> ブラウザ上ではブックマークレットで、＜p＞タグの中身を順にローカルサーバーにGetで送る。

というのがあったので

＜p＞
元の文書
＜/p＞

(「＜＞」は半角で)
として拡張子をhtmlにしてブラウザで開くのが

>> コマンドプロンプトが開いたまま、翻訳したい論文ページをブラウザで開く。

の「翻訳したい論文ページをブラウザで開く」に該当すればと思ったのです。
実際は、「論文ページ」と同じつくりじゃないと駄目なのかもしれません。

また、以下のようにしてないといけないかもしれません。
＜html＞
＜head＞文書のヘッダ部分＜/head＞これはいらないかも
＜body＞
＜p＞
元の文書
＜/p＞
＜/body＞
＜/html＞

これでうまくいけば、テキストファイルに出力するときにタグを入れ込んで、拡張子をhtmlで保存するようにすればいいですね。

あと「順に～送る」とあったので元の文書を分割せずに、分けたい場所を＜p＞＜/p＞で囲んでやるといいのかもしれません。

以上、私が感じた部分です。全然勘違いの可能性も大ですが…。

お礼

kkkkkmさん、OMISOの利用方法の具体的な手順をおしえていただきありがとうございます。

その後、手順を「あーでもない、こうかな、これではどうか、それではこれで」的に色々模索しましたが
結果的には翻訳が出来ませんでした。

DeepLの仕様で、無料版（Pro版でない場合）では、
　「テキストの翻訳」の場合登録ユーザーの場合１度に翻訳できる文字数が5000文字までと規制されています。

それとは別に「ファイルの翻訳」と言う機能が利用できるのですが、
　これも無料版（Pro版でない場合）では、1か月に3ファイルまでの規制があります。
　　　（１ファイルは10万語まで）

OMISOは、どうも「ファイルの翻訳」の方を利用していているようで
　手順にしたがってテストで使用すると「Omiso翻訳」をクリックして数秒すると

　「このページの内容
　　　　DeepLの翻訳上限に達した可能性があります。」

と表示されて門前払いされている状況です。

多分、今月数回「ファイルの翻訳」を利用したので上限に達したのだと思われます。
（参考にした、URL　https://eigo-no-manma.com/deepl-subtitle-translation）

「テキストの翻訳」では、現在も上限以内なら問題なく利用できています。

OMISOを使って制限文字数（5000文字）以内のテキストファイルをOMISOで翻訳させる事を想定していたのですが
現状では、どうも無理そうです。

色々お世話になったのに残念な結果です。

回答No.11

kkkkkm

> 突き詰めていくとコードで書式をテキストに変換する手段では
> 思わぬところで不具合が発生しそうなので
> 最初からA列を書式設定で文字列で登録していた方が良さそうですね。

どのようなデータが出てくるか考えが及ばないので、どんなデータでもOkとは言い難く、データが入っているセルのままどうにかするより、最初から空のセルを文字列で書式設定して、そこにデータを入れる方が間違いがなさそうですね。

> これを一つづつ手動でDeepLに処理させるのは厳しそうなので
> やはり自動化が必要です。

先のリンク先を見てみたのですが、何となくですが、テキストデータをHTMLにして＜p＞タグで囲ってやるといけそうですね。

お礼

関連先（TranslationGummyOmiso）のURLをチェックいただきありがとうごじます。

>先のリンク先を見てみたのですが、
>何となくですが、テキストデータをHTMLにして＜p＞タグで囲ってやるといけそうですね。

「テキストデータをHTMLにして＜p＞タグで囲ってやるといけそうですね」

知識がなくどうやれば書き出したテキストファイルをOMISOに引き継いで翻訳させるかが良くわかりません。

初歩の初歩でしょうが、HTMLに変換する方法を教えてください。

’--------------------------------------------------------

URLには、以下のように記載されています。

導入方法
「ここをクリック」してzipファイルをダウンロードし、解凍する。
　　　　　---　解凍までまで完了済み

ブラウザで「このリンク」を開き、指示に従ってブックマークレットを登録する。
　　　　　---　リンクをブックマークバーにドラッグ&ドロップしてGoogleに登録済み(omiso翻訳の表示確認済み）

以下の「セキュリティ的にダウンロードができない場合」は操作が理解できていないので行っていません。

セキュリティ的にダウンロードができない場合

「OmisoServer.ps1」,「StartOmisoServer.bat」を開く。
右上の「Raw」からプログラムを表示して、それぞれを別のメモ帳にコピペする。
それぞれを「OmisoServer.ps1」「StartOmisoServer.bat」の名前で保存する。

以下の使用方法は、翻訳したいテキストファイルをHTMLに変換する必要があるとの
kkkkkmさんのアドバイスがあるので実行まで至っていません。

使用方法

ダウンロードしたディレクトリにある「StartOmisoServer.bat」をクリックすると、コマンドプロンプトが開く。
コマンドプロンプトが開いたまま、翻訳したい論文ページをブラウザで開く。
先ほど作成した「Omiso翻訳」ブックマークを開くと、10秒ちょっとで翻訳される。

回答No.10

kkkkkm

もし、セルの値が日付だったり時刻だったりした場合は、現状では「文字列」に設定した途端にシリアル値がそのまま文字列になるので、その可能性があるのでしたら、以下にしておいた方が無難かもしれません。
現状は全てのセルでデータを再入力してますが、以下は「=」がある場合とない場合で分けています。
If cell.NumberFormat <> "@" Thenは、一応そのままにしてます。

For Each cell In Range("A1:A" & lastRow)
If cell.NumberFormat <> "@" Then 'A列のセル書式設定が文字列で無い場合
tmp = cell.Text
cell.NumberFormatLocal = "@" 'セルの書式をテキストに変更
If Left(cell.Formula, 1) = "=" Then 'セルのフォーミュラが"="で始まる場合
cell.Value = Mid(cell.Formula, 2) 'セルの値をフォーミュラの2番目以降の文字列に変更
Else
cell.Value = tmp
End If
End If
Next cell

お礼

kkkkkmさん、何度もコードの修正ありがとうございます。

気にかけていただいて点などを考慮すると
突き詰めていくとコードで書式をテキストに変換する手段では
思わぬところで不具合が発生しそうなので
最初からA列を書式設定で文字列で登録していた方が良さそうですね。

実際のDATAでテキストを分割したら連番が20個を超えてしまいました
これを一つづつ手動でDeepLに処理させるのは厳しそうなので
やはり自動化が必要です。

回答No.9

imogasi

#3です。漏れを修正させてください。
Ａ列セルに色付けするとき、(楽なので）Colorindexを使ってますが、この値は使えるのが３５色（種）までなので、
If j>35 then
j=1
end if
を入れるべき、でした。

お礼

imogashiさん、興味を持っていただきありがとうございます。

＞やって見ただけ（説明文の用意など）数時間必要だった）で、自分にとって新しいものは無いやり方となった。

貴重な時間を消費させるだけの結果になったようなのでごめんなさい。

回答No.8

kkkkkm

以下の部分について書き忘れてました。

> If cell.NumberFormat <> "@" Then 'A列のセル書式設定が文字列で無い場合

この部分ですが、もし「-abc」が「=-abc」になっていて、今回のコードを実行する前に、そのままの状態で文字列に設定してしまった場合(手動で設定したなど)、データは「=-abc」のまま設定が文字列なので、今回のコードで「-abc」の文字列には変換されないと思います。

多分、そのままの状態で文字列に設定されていたというような事は無いと思いますが、気にしておいた方がいいと思います。