Import .csv file

[DragonWood]

Greetings,


I'm sure the solution is already posted somewhere, but I cannot find it.


I need to import a .csv file. It will always have the same number of columns (7), but the number of rows will vary from 8 (minimum as this is the header size) to an unknown amount. So, essentially, A1:G?


I'm looking for a code to allow me to select the file with the data, then have it find the number of rows, copy that data, and paste it (Values Only) into my workbook.


Since this will be used by more than just myself the name and location of the source data is going to change.


I got this code when I recorded a macro while I performed the steps.




Option Explicit


Sub Macro1()
'
' Macro1 Macro
'


'
ChDir "C:\Users\User\Documents\Projects\Main Excel Workbook"
Workbooks.Open Filename:= "C:\Users\User\Documents\Projects\Source.csv"
Range("A1:G151").Select
Selection.Copy
Windows("Main Excel Workbook.xlsm").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


End Sub



I understand the basics of what this code does. I am just not sure how to change it to make sure it is pasting the data into the correct place.


It needs to be saved on the "Log" page of the Main Excel Workbook.


And it needs to start the paste at cell A2 since I have added a header title for the data to the first row of that page.


I appreciate any help I can get.


Thanks.

[Paul_Hossler]

There's a lot more potential automation, but this is the basics

It always puts the data starting at A2. If it's a on-going log sheet then you might want to start at the first blank line on 'Log'





Option Explicit

Sub GetFile()
Dim logWB As Workbook, csvWB As Workbook
Dim logSheet As Worksheet
Dim csvName As String

'get file name
csvName = Application.GetOpenFilename("Log Files (*.csv), *.csv")
If csvName = "False" Then Exit Sub

Application.ScreenUpdating = False

Set logWB = ThisWorkbook
Set logSheet = logWB.Worksheets("Log")

'open csv
Workbooks.Open Filename:="C:\Users\USERID\Desktop\FILENAME.csv"
Set csvWB = ActiveWorkbook

'copy data
ActiveSheet.Cells(1, 1).CurrentRegion.Copy logSheet.Cells(2, 1)

'close CSV, skip clip board waring
Application.DisplayAlerts = False
csvWB.Close False
Application.DisplayAlerts = True

'activate log workbook
logWB.Activate
Application.ScreenUpdating = True

Call MsgBox(csvName & " copied", vbOKOnly + vbInformation, "GetFile")
End Sub

[DragonWood]

Thanks Paul,


I was finally able to try your code. I keep getting an error at this point:


Workbooks.Open Filename:="C:\Users\USERID\Desktop\FILENAME.csv"



I commented that out and ran it again and it locked everything up to where there was nothing but a grey screen on Excel and all my VBA was blank. When I close Excel and re-open the file it is fine, but it isn't working.




I was looking through some of my refereneces from other projects I've worked on where I had to import something. Those were all importing from a .txt file, not a .csv file though.


I made a few changes to one of them using your code as a reference.


This is what I ended up with, but I keep getting an error at this point:


wbTemperature = Application.GetOpenFilename("Log Files (*.csv), *.csv")



Which is misleading because it lets me chose the file I want to import from before giving me the error. Which I wasn't able to make happen before for some reason.


Here is my entire code as it sits right now. I'm still playing with it trying to figure it out.






Function ImportTempLog()
'Imports the temperature log data.


Dim wbMaster As Workbook
Dim wbTemperature As Workbook
Dim wsImport As Worksheet
Dim ws As Worksheet
Dim aTemperature As Variant
Dim iTemperature As Long


'Call ResetTemperatureLog


Set wbMaster = ThisWorkbook
Set wsImport = wbMaster.Worksheets("Temperature Log")


'Ask for temperature workbook name.
wbTemperature = Application.GetOpenFilename("Log Files (*.csv), *.csv")
If wbTemperature = "False" Then Exit Function

Set wbTemperature = ActiveWorkbook

ActiveSheet.Cells(1, 1).CurrentRegion.Copy
wbMaster.Activate
wsImport.Visible = True
wsImport.Select
wsImport.Cells(2, 1).Select
'wsImport.Cells(wsImport.Rows.Count, 2).End(x1Up).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
wsImport.Visible = False




' 'If Not IsArray(aTemperature) Then Exit Function
'
' Application.ScreenUpdating = False
' Application.EnableEvents = False
' For iTemperature = LBound(wbTemperature) To UBound(wbTemperature)
'
' 'Open each wb
' Workbooks.Open Filename:=wbTemperature(iTemperature)
' Set wbTemperature = ActiveWorkbook
'
' 'Look for special marker on each sheet (one / WB)
' For Each ws In wbTemperature.Worksheets
' If ws.Cells(1, 1).Value = "Start Time" Then
' ws.Cells(1, 1).CurrentRegion.Columns(7).Copy
' wbMaster.Activate
' wsImport.Visible = True
' wsImport.Select
' wsImport.Cells(wsImport.Rows.Count, 2).End(x1Up).Offset(1, 0).Select
' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' wsImport.Visible = False
'
' Exit For
' End If
'
' Next
'
' wbTemperature.Close (False)
'
' Next iTemperature


ActiveWorkbook.Worksheets("Temperature Log").Visible = True


Application.GoTo Sheets("Temperature Log").Range("A1")


Application.ScreenUpdating = True
Application.EnableEvents = True


End Function



Yes, I am applying it as a Function and not a Sub. That is because I am calling it from a button that I have placed on the Excel Ribbon. I intend to have that button actually do something else before activating this code so I don't have it calling the Function directly. Instead, it called another Sub where I tell it what else to do before calling this code.


Thanks for the help, I hope you can help more.

[DragonWood]

Here is a copy of the File I'm trying to import from, if that helps. My master workbook is too large to upload.

[Paul_Hossler]

1. I messed up and left in some testing

2. GetOpenFilename returns a string. Use that to open a workbook

3. The CSV was all values, so I don't think you need the PasteSpecial, just regular old Copy/Paste

4. When you close the CSV (without saving), I usually like to .Activate the previous WB just to be sure





Option Explicit

'Imports the temperature log data.
Function ImportTempLog()
Dim wbMaster As Workbook
Dim wbTemperature As Workbook
Dim sTemperature As String
Dim wsImport As Worksheet

'Call ResetTemperatureLog
Set wbMaster = ThisWorkbook
Set wsImport = wbMaster.Worksheets("Temperature Log")

'Ask for temperature workbook name.
sTemperature = Application.GetOpenFilename("Log Files (*.csv), *.csv")
If sTemperature = "False" Then Exit Function

Application.ScreenUpdating = False

Workbooks.Open sTemperature
Set wbTemperature = ActiveWorkbook

ActiveSheet.Cells(1, 1).CurrentRegion.Copy wsImport.Cells(2, 1)
wbTemperature.Close False
wbMaster.Activate
wsImport.Select
Application.GoTo wsImport.Range("A1")

Application.ScreenUpdating = True

End Function

[DragonWood]

Thanks again Paul.


Now I'm getting an error at this point.




ActiveSheet.Cells(1, 1).CurrentRegion.Copy wsImport.Cells(2, 1)



So you can get a better idea of what I'm trying to accomplish. I have included a copy of the Temperature Log page from my workbook. There is no VBA code in this right now.


The button on the ribbon is going to call a form where the user can determine if they are importing the "Before" or the "After" temperature.


Once I had the import code working, I was going to modify it accordingly and make two functions, one for each the Before and After which would then be called by the form.


While, technically, I only need to replace what is in row 3 down, because row two is a "header" row. But since that is part of the file being imported I figured it was easier to just import everything.

[Paul_Hossler]

Well, using your Temperature.csv (http://www.vbaexpress.com/forum/attachment.php?attachmentid=21995&d=1523299459) from post #4, my macro from post #5, and your XLSM from post #6, this is what I get with no errors

There are more readings than in your XLSM example, but I figured that was because the CSV had more lines

[DragonWood]

Paul,


I was still getting the error, but I figured out why.


The page was protected and the cells were locked.


I wasn't considering this to be an issue because the code that locks my workbook is supposed to allow code to make changes, no matter if the cells are locked or not.


Here is a copy of that code for your reference:




Private Sub Workbook_Open()
'Sets the password for each Worksheet, but still allows the code to work.


Dim wkSheet As Worksheet


For Each wkSheet In Worksheets
Select Case wkSheet.Name
Case "Pager Log Sheet", "Daily Activities", "Time & Mileage", "Cheat Sheet", "Full List"
'do nothing
Case Else
wkSheet.Protect "Hex@Met", UserInterfaceOnly:=True
End Select
Next wkSheet


Call ReadInstructions
Call ShowDefaults


End Sub







What I did was add this code to the beginning of what you provided. It works fine now.




With ActiveWorkbook.Worksheets("Temperature Log")
.Visible = True
.Range("A2:G500").Select
Selection.Locked = False
End With


Thanks for all the help.