質問編集履歴
5
ｋ
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -172,7 +172,7 @@
|
|
|
172
172
|
|
|
173
173
|
|
|
174
174
|
|
|
175
|
-
MsgBox "先程選択した、
|
|
175
|
+
MsgBox "先程選択した、データを選択してください"
|
|
176
176
|
|
|
177
177
|
With CreateObject("ADODB.Stream")
|
|
178
178
|
|
4
やったことの追記
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -156,6 +156,80 @@
|
|
|
156
156
|
|
|
157
157
|
|
|
158
158
|
|
|
159
|
+
|
|
160
|
+
|
|
161
|
+
### やったこと
|
|
162
|
+
|
|
163
|
+
```VBA
|
|
164
|
+
|
|
165
|
+
Dim buf As String, Target As String, i As Long
|
|
166
|
+
|
|
167
|
+
Dim records As Variant, items As Variant, j As Long
|
|
168
|
+
|
|
169
|
+
Dim Csv_Import_File ' Excelファイルに取り込むCSVファイルの名前を入れ込む変数
|
|
170
|
+
|
|
171
|
+
Csv_Import_File = Application.GetOpenFilename("CSVファイル,*.csv") ' CSVファイルを選択する
|
|
172
|
+
|
|
173
|
+
|
|
174
|
+
|
|
175
|
+
MsgBox "先程選択した、shopify返品データを選択してください"
|
|
176
|
+
|
|
177
|
+
With CreateObject("ADODB.Stream")
|
|
178
|
+
|
|
179
|
+
.charset = "Shift-JIS"
|
|
180
|
+
|
|
181
|
+
.Open
|
|
182
|
+
|
|
183
|
+
.LoadFromFile Csv_Import_File
|
|
184
|
+
|
|
185
|
+
buf = .ReadText
|
|
186
|
+
|
|
187
|
+
.Close
|
|
188
|
+
|
|
189
|
+
records = Split(buf, "#")
|
|
190
|
+
|
|
191
|
+
headers = Split(records(0), ",")
|
|
192
|
+
|
|
193
|
+
|
|
194
|
+
|
|
195
|
+
' header書き出し
|
|
196
|
+
|
|
197
|
+
For j = 0 To UBound(headers)
|
|
198
|
+
|
|
199
|
+
Worksheets(targetsheet).Cells(1, j + 1).Value = headers(j)
|
|
200
|
+
|
|
201
|
+
Next j
|
|
202
|
+
|
|
203
|
+
|
|
204
|
+
|
|
205
|
+
'カンマをセミコロンに置換
|
|
206
|
+
|
|
207
|
+
buf = ReplaceSeparator(buf, ",", ";")
|
|
208
|
+
|
|
209
|
+
buf = Replace(buf, """", "") '引用符(")を削除
|
|
210
|
+
|
|
211
|
+
|
|
212
|
+
|
|
213
|
+
records = Split(buf, "#") '#で分割して配列に
|
|
214
|
+
|
|
215
|
+
For i = 0 To UBound(records)
|
|
216
|
+
|
|
217
|
+
If i > 0 Then records(i) = "#" & records(i)
|
|
218
|
+
|
|
219
|
+
items = Split(records(i), ";") 'セミコロンで分割して配列に
|
|
220
|
+
|
|
221
|
+
'配列をセル範囲に代入
|
|
222
|
+
|
|
223
|
+
Worksheets(1).Cells(i + 1, 1).Resize(, UBound(items) + 1).Value = items
|
|
224
|
+
|
|
225
|
+
Next
|
|
226
|
+
|
|
227
|
+
End With
|
|
228
|
+
|
|
229
|
+
```
|
|
230
|
+
|
|
231
|
+
|
|
232
|
+
|
|
159
233
|
教えていただいたURL参考に行いましたが、CSVの値がすべて消えてしまいます。
|
|
160
234
|
|
|
161
235
|
recordsの中身がない状態です
|
3
エラー内容の追記
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -119,3 +119,45 @@
|
|
|
119
119
|
|
|
120
120
|
|
|
121
121
|
"Tokyo3-369, Tokyo2F" こちらの個所からずれが発生いたします
|
|
122
|
+
|
|
123
|
+
|
|
124
|
+
|
|
125
|
+
### 対応したこと
|
|
126
|
+
|
|
127
|
+
```VBA
|
|
128
|
+
|
|
129
|
+
For i = 1 To UBound(records)
|
|
130
|
+
|
|
131
|
+
items = Split(records(i), ",")
|
|
132
|
+
|
|
133
|
+
For j = 0 To UBound(items)
|
|
134
|
+
|
|
135
|
+
If j = 0 And i <> 0 Then
|
|
136
|
+
|
|
137
|
+
Worksheets(targetsheet).Cells(i + 1, j + 1).Value = "#" + items(j)
|
|
138
|
+
|
|
139
|
+
Else
|
|
140
|
+
|
|
141
|
+
' Worksheets(targetsheet).Cells(i + 1, j + 1).Value = Replace(items(j), """", "")
|
|
142
|
+
|
|
143
|
+
Worksheets(targetsheet).Cells(i + 1, j + 1).Value = Split(Replace(replaceColon1(strLine), """", ""), ":")
|
|
144
|
+
|
|
145
|
+
End If
|
|
146
|
+
|
|
147
|
+
Next j
|
|
148
|
+
|
|
149
|
+
Call write_target(i, items, headers)
|
|
150
|
+
|
|
151
|
+
Next i
|
|
152
|
+
|
|
153
|
+
```
|
|
154
|
+
|
|
155
|
+
End With
|
|
156
|
+
|
|
157
|
+
|
|
158
|
+
|
|
159
|
+
教えていただいたURL参考に行いましたが、CSVの値がすべて消えてしまいます。
|
|
160
|
+
|
|
161
|
+
recordsの中身がない状態です
|
|
162
|
+
|
|
163
|
+
[リンク内容](https://tonari-it.com/vba-csv-camma/)
|
2
補足
test
CHANGED
|
@@ -1 +1 @@
|
|
|
1
|
-
VBA CSVを取り込む際に、
|
|
1
|
+
VBA CSVを取り込む際に、ダブルクォーテーション内のカンマを削除なり、置換をしたいです
|
test
CHANGED
|
@@ -1,6 +1,8 @@
|
|
|
1
1
|
CSVを取込み、エクセルシートに表示をすると値がずれてしまいます。
|
|
2
2
|
|
|
3
3
|
以下のコードを使用して取り込みましたが項目内のカンマで値がずれてしまいます・・
|
|
4
|
+
|
|
5
|
+
ダブルクォーテーション内のカンマを削除なり、置換をしたいです
|
|
4
6
|
|
|
5
7
|
replace等も使用しましたが、上手くいきません
|
|
6
8
|
|
1
補足
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -106,8 +106,14 @@
|
|
|
106
106
|
|
|
107
107
|
Name,Email,Financial Status,Paid at,Fulfillment Status,Fulfilled at,Accepts Marketing,Currency,Subtotal,Shipping,Taxes,Total,Discount Code,Discount Amount,Shipping Method,Created at,Lineitem quantity,Lineitem name,Lineitem price,Lineitem compare at price,Lineitem sku,Lineitem requires shipping,Lineitem taxable,Lineitem fulfillment status,Billing Name,Billing Street,Billing Address1,Billing Address2,Billing Company,Billing City,Billing Zip,Billing Province,Billing Country,Billing Phone,Shipping Name,Shipping Street,Shipping Address1,Shipping Address2,Shipping Company,Shipping City,Shipping Zip,Shipping Province,Shipping Country,Shipping Phone,Notes,Note Attributes,Cancelled at,Payment Method,Payment Reference,Refunded Amount,Vendor,Outstanding Balance,Employee,Location,Device ID,Id,Tags,Risk Level,Source,Lineitem discount,Tax 1 Name,Tax 1 Value,Tax 2 Name,Tax 2 Value,Tax 3 Name,Tax 3 Value,Tax 4 Name,Tax 4 Value,Tax 5 Name,Tax 5 Value,Phone,Receipt Number,Duties,Billing Province Name,Shipping Province Name,Payment Terms Name,Next Payment Due At
|
|
108
108
|
|
|
109
|
-
#1041,addres,pending,,unfulfilled,,yes,JPY,5500,0,0,5500,"",0,送料,2021-07-21 20:45:23 +0900,1,AAAA N,5500,,062906810,true,false,pending,name,"Tokyo3-369, Tokyo2F",Tokyo3-369,Tokyo4F,,江東区,107-0000,JP-13,JP,'+81 00-0000-0000,00000000 : 111 ,"指定: なし
|
|
109
|
+
#1041,addres,pending,,unfulfilled,,yes,JPY,5500,0,0,5500,"",0,送料,2021-07-21 20:45:23 +0900,1,AAAA N,5500,,062906810,true,false,pending,name,**"Tokyo3-369, Tokyo2F"**,Tokyo3-369,Tokyo4F,,江東区,107-0000,JP-13,JP,'+81 00-0000-0000,00000000 : 111 ,"指定: なし
|
|
110
110
|
|
|
111
111
|
指定: 指定なし",,Ca"",0,A,30,,,,42348,"",Low,web,0,,,,,,,,,,,,,,東京都,東京都,,
|
|
112
112
|
|
|
113
|
+
|
|
114
|
+
|
|
113
115
|
```
|
|
116
|
+
|
|
117
|
+
|
|
118
|
+
|
|
119
|
+
"Tokyo3-369, Tokyo2F" こちらの個所からずれが発生いたします
|