My fellow excelorians, I am trying to reference a CSV file via my macro but keep getting the Run-time Error '438':
I have tried various code changes to try and resolve the issue without success.
It appears that the sheet name "order_items_Orders" from the CSV files is not being allocated to the variable "wsCSV"
Thanks in advance for any assistance.
The code I am trying to execute is as follows, it messy at the moment because its not finished:
Public Sub OrdersAutoEntry()
'
' Created by Woftae
'
Dim FilePathCSV As String
'FilePathCSV = "C:\Users\woftae\downloads\order_items_Orders.csv"
'MsgBox ActiveWorkbook.Name & vbNewLine & ActiveWorkbook.Path
Dim wbCSV As Workbook
Set wbCSV = Workbooks.Open(FilePathCSV)
Dim wsCSV As Worksheet
Set wsCSV = wbCSV.Worksheets("order_items_Orders")
'Set wsCSV = Workbooks("order_items_Orders.csv").Worksheets("order_items_Orders")
MsgBox "CSV sheet is: " & wsCSV <<<<<< error occurs here
'Set ws = wb.Sheets(1)
Dim RowCSV As Long 'Orders Exported Sheet Row
RowCSV = 2
Dim wsORDERS As Worksheet
Set wsORDERS = Workbooks("Suppliers Orders V3.xlsm").Worksheets("Orders")
Dim RowORDERS As Long 'Orders Sheet Row
RowORDERS = wsCSV.Range("B" & Rows.Count).End(xlUp).Row + 1 ' Needs to be set to last row of the orders sheet
'MsgBox ActiveWorkbook.Name & vbNewLine & wsCSV
wsCSV.Select
Do While wsCSV.Cells(RowCSV, 2).Value <> "" And wsCSV.Cells(RowCSV, 9).Value <> 0
wsORDERS.Range("B" & RowORDERS).Value = wsCSV.Cells(RowCSV, 2)
wsORDERS.Range("D" & RowORDERS).Value = wsCSV.Cells(RowCSV, 4)
wsORDERS.Range("E" & RowORDERS).Value = wsCSV.Cells(RowCSV, 5)
wsORDERS.Range("F" & RowORDERS).Value = wsCSV.Cells(RowCSV, 6)
wsORDERS.Range("H" & RowORDERS).Value = wsCSV.Cells(RowCSV, 8)
wsORDERS.Range("I" & RowORDERS).Value = wsCSV.Cells(RowCSV, 9)
RowCSV = RowCSV + 1
RowORDERS = RowORDERS + 2
Loop
wsCSV.Cells.Select
Selection.ClearContents
wsCSV.Range("B" & RowORDERS).Select
End Sub
I have tried various code changes to try and resolve the issue without success.
It appears that the sheet name "order_items_Orders" from the CSV files is not being allocated to the variable "wsCSV"
Thanks in advance for any assistance.
The code I am trying to execute is as follows, it messy at the moment because its not finished:
Public Sub OrdersAutoEntry()
'
' Created by Woftae
'
Dim FilePathCSV As String
'FilePathCSV = "C:\Users\woftae\downloads\order_items_Orders.csv"
'MsgBox ActiveWorkbook.Name & vbNewLine & ActiveWorkbook.Path
Dim wbCSV As Workbook
Set wbCSV = Workbooks.Open(FilePathCSV)
Dim wsCSV As Worksheet
Set wsCSV = wbCSV.Worksheets("order_items_Orders")
'Set wsCSV = Workbooks("order_items_Orders.csv").Worksheets("order_items_Orders")
MsgBox "CSV sheet is: " & wsCSV <<<<<< error occurs here
'Set ws = wb.Sheets(1)
Dim RowCSV As Long 'Orders Exported Sheet Row
RowCSV = 2
Dim wsORDERS As Worksheet
Set wsORDERS = Workbooks("Suppliers Orders V3.xlsm").Worksheets("Orders")
Dim RowORDERS As Long 'Orders Sheet Row
RowORDERS = wsCSV.Range("B" & Rows.Count).End(xlUp).Row + 1 ' Needs to be set to last row of the orders sheet
'MsgBox ActiveWorkbook.Name & vbNewLine & wsCSV
wsCSV.Select
Do While wsCSV.Cells(RowCSV, 2).Value <> "" And wsCSV.Cells(RowCSV, 9).Value <> 0
wsORDERS.Range("B" & RowORDERS).Value = wsCSV.Cells(RowCSV, 2)
wsORDERS.Range("D" & RowORDERS).Value = wsCSV.Cells(RowCSV, 4)
wsORDERS.Range("E" & RowORDERS).Value = wsCSV.Cells(RowCSV, 5)
wsORDERS.Range("F" & RowORDERS).Value = wsCSV.Cells(RowCSV, 6)
wsORDERS.Range("H" & RowORDERS).Value = wsCSV.Cells(RowCSV, 8)
wsORDERS.Range("I" & RowORDERS).Value = wsCSV.Cells(RowCSV, 9)
RowCSV = RowCSV + 1
RowORDERS = RowORDERS + 2
Loop
wsCSV.Cells.Select
Selection.ClearContents
wsCSV.Range("B" & RowORDERS).Select
End Sub