I have a csv file always named the same, called SO2PO.csv. It has data that I import into an excell sheet called PO Data, in a workbook called Open Order. I need to find a way to import all the data from SO2PO.csv to Open Order.xlsm sheet PO Data.
I know it's possible, but how? Can someone point me in the right direction?
Or is there a way to make it so that I can import any .csv file that in placed into a specific folder?
Macro to parse a csv or txt file You can copy the code and insert it into a VBA module. Just highlight it with the mouse, press CTRL+C and insert with CTRL+V. If you are viewing this page on a small screen, some of the code lines may appear "broken," but they will be okay when you paste into a VBA module. That was it.
Power Automate allows you to open comma-delimited and tab-delimited CSV files directly through the Launch Excel action. To convert a CSV file of this type to XLSX, open the file and then save it as a new Excel workbook using the Save document as option in the Close Excel action.
Add this code to create a QueryTable in the PO Data sheet to your data source
Once you have created the QueryTable you can then just right click Refresh the data (or refresh on open)
Sub CSV_Import()
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("PO Data") 'set to current worksheet name
strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")
With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End With
End Sub
If you're going to use querytables make sure you clean up after, leftover query tables caused me a few headaches in a downstream process.
' get the file to the data sheet
Set ws = ActiveWorkbook.Sheets("Data")
With ws.QueryTables.Add(Connection:="TEXT;" & "mydata.csv", Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End With
' delete the querytable if there is one
On Error GoTo nothingtodelete
Sheets("Data").QueryTables(1).SaveData = False
Sheets("Data").QueryTables.Item(1).Delete
nothingtodelete:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With