Macro to copy from CSV to table in workbook

[theath]

Trying to write a script and having trouble getting it to work.


Export.CSV

Every month a CSV file will be made available. The number of rows are variable every month, but the columns contain data from A to AS. The name of this export will change and reflect the month the data is from.


Report.XLSM

I have another spreadsheet into which I would like to import this data with a macro.


Macro

I would like to run a macro in Report.XLSM, have it prompt for the location of the report containing the data (Export.csv), copy all of the data, paste it into the "Data" table on the Data sheet in the Report.XLSM workbook.


Here is what I have so far. I am a newbie, so this might be pretty messed up.


Thanks for any help you can offer.


Sub copyData()

Dim fn

Dim wbFrom As Workbook

Dim ws As Worksheet

Dim rCopy As Range

Dim sSht As String

On Error Resume Next

fn = Application.GetOpenFilename

If fn = False Then

MsgBox "Nothing Chosen", vbCritical, "Select workbook"

Else: Workbooks.Open fn

Set wbFrom = ActiveWorkbook

For Each ws In wbFrom.Worksheets

With ws

sSht = .Name

'determine range to copy

Set rCopy = .Range("A1").Select

Range(Selection, Selection.End(xlToRight)).Select

Range(Selection, Selection.End(xlDown)).Select

End With

If Not rCopy Is Nothing Then rCopy.Copy _

ThisWorkbook

Sheets("Data").Select

Range("Table1[[#Headers],[Project Number]]").Select

Selection.End(xlDown).Select

Next ws

End If

On Error GoTo 0

Set rCopy = Nothing

Set wbFrom = Nothing

End Sub

 

[theath]

I have made it a bit futher. Now I am stuck at this:


I declare WB as ThisWorkbook. Later, I want to go back to WB and select the DATA tab and paste my data.


When I get to the Paste sub routine it errors out


Sub importData()


Dim WB As Workbook

Set WB = ThisWorkbook


getData

Copy

Paste

End Sub


Sub getData()

Dim fn

Dim wbFrom As Workbook

Dim ws As Worksheet

Dim sSht As String

On Error Resume Next

fn = Application.GetOpenFilename

If fn = False Then

MsgBox "Nothing Chosen", vbCritical, "Select workbook"


Else: Workbooks.Open fn


' Set wbFrom = ActiveWorkbook


End If


'Set wbFrom = Nothing


End Sub

Sub Copy()


Range("A1").Select

Range(Selection, Selection.End(xlToRight)).Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Copy

End Sub


Sub Paste()

WB.Sheets("DATA").Select

'ThisWorkbook.Worksheets.Select

Sheets("DATA").Select

Range("Table1[Project Number]").Select

Range("A1").Select

ActiveSheet.Paste


End Sub

 

[Wulluby]

Just had to do something similair, I used the following which is probably not the cleanest but the import itself came from just hitting record macro button.


'Opens file browser to select file

Dim fn

fn = Application.GetOpenFilename

If fn = False Then

End

End If


'Creates worksheet

Dim Data_Import As Worksheet

Set Data_Import = Sheets.Add

Data_Import.Name = "Data Import"


Application.ScreenUpdating = False

Application.DisplayAlerts = False


'Imports semicolon delimited data from file

Sheets("Data Import").Select

With ActiveSheet.QueryTables.Add(Connection:= _

"TEXT;" & fn, Destination:=Range("$A$1"))

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.TextFilePromptOnRefresh = False

.TextFilePlatform = 850

.TextFileStartRow = 1

.TextFileParseType = xlDelimited

.TextFileTextQualifier = xlTextQualifierDoubleQuote

.TextFileConsecutiveDelimiter = False

.TextFileTabDelimiter = True

.TextFileSemicolonDelimiter = True

.TextFileCommaDelimiter = False

.TextFileSpaceDelimiter = False

'The 9th in the array set to 3 to import Start Date as MDY

.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 3, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _

1, 1, 1)

.TextFileTrailingMinusNumbers = True

.Refresh BackgroundQuery:=False

'Formats Start date to dd-mmm-yy

Columns("I:I").Select

Selection.NumberFormat = "d-mmm-yy"

End With


Application.ScreenUpdating = True

Application.DisplayAlerts = True


*Edit: There was some date format jiggery pokery in there that I guess you can cut out.

 

[theath]

Is there a way to change just the paste function?

 

[Hui]

Theath


You have your Dim's inside a few of the subroutines

This causes them to be local to that subroutine

You may want to try and put the Dim's in front of the First Subroutine


like


Dim WB As Workbook


Sub importData()

Set WB = ThisWorkbook

.

.

.

End Sub


This way once WB is set it is available to all other subroutines

You need to put all Variables that you wish to share like this in front of the top Subroutine