Year: 2020

Data Consolidation

Agnieszka

Just read that Microsoft will support Visual Basic on .NET 5.0 but will no longer add new features or evolve the language.

Does it mean the end? Not really, but as much as I’ve already learned about MS business strategy it might be awarness and first signs to think about other environment of data processing. And I am working on something new :).

Still I want to share with you some VB knowledge, especially advances about VBA in Acces, so this one is probably the last post showing examples of Excel macro.

1.From import level with dedicated path

Let’s say we have some (Excel) files located on local path and we need to import all data to one sheet.

paths
Dim CurrSheet As Worksheet
Dim ReadFile As Workbook
Dim FilePath As String
Dim FileName As String
Dim rCounter As Long
Dim iCounter As Integer

Application.ScreenUpdating = False
Const Odstep = 1

Set CurrSheet = ThisWorkbook.Worksheets(1)
FilePath = "C:\Users\Agnieszka\Desktop\files\" 
FileName = Dir(FilePath & "*.xlsx")

With CurrSheet
Do While FileName <> ""
rCounter = .Cells(.Rows.Count, "A").End(xlUp).Row + 1

Set ReadFile = Workbooks.Open(FilePath & FileName)
ReadFile.Worksheets(1).Cells(1, 1).CurrentRegion.Copy .Cells(rCounter, "A")
ReadFile.Close
FileName = Dir
Loop
End With

Quite nice, but we need something else: remove special spaces and headers copies

iCounter = 3
Do While ActiveSheet.Cells(iCounter, 1) <> ""
If ActiveSheet.Cells(iCounter, 1) = "Name" Then Rows(iCounter).Delete
iCounter = iCounter + 1
Loop

And just one line at the begining to refersh data :

Cells.ClearContents

2. Inside the Workbook – between 2 sheets

Dim Sheet1, Sheet2, SheetAll As Worksheet
Dim Lst1, Lst2, WherePaste, FrstRow, Cols

Set Sheet1 = ThisWorkbook.Worksheets("Data1")
Set Sheet2 = ThisWorkbook.Worksheets("Data2")
Set SheetAll = ThisWorkbook.Worksheets("DataAll")
Lst1 = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
Lst2 = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
WherePaste = SheetAll.Cells(Rows.Count, "A").End(xlUp).Row + 1

If WherePaste = 1 Then WherePaste = 2
FrstRow = 2
Cols = 4
Application.ScreenUpdating = False

With Sheet1
.Range(.Cells(FrstRow, "A"), .Cells(Lst1, Cols)).Copy _
SheetAll.Cells(WherePaste, "A")
End With

WherePaste = SheetAll.Cells(Rows.Count, "A").End(xlUp).Row + 1

With Sheet2
.Range(.Cells(FrstRow, "A"), .Cells(Lst2, Cols)).Copy _
SheetAll.Cells(WherePaste, "A")
End With

Dim i As Integer
For i = 1 To 4
Sheets(3).Cells(1, i).Value = Sheets(1).Cells(1, i).Value
Next i

[VBA]Excel MiniProject2 vol.1

Agnieszka

Time for another summary of my Excel posts. Best way to repeat the information is to make a little project with modules for import, export and some operations on values.

At the beginning I would like to create a folder, perhaps on my Desktop with source file already inside this path.

First module will be called mImportExport with follwoing procedures:

Sub ImportCSVFile()

Already I have presented a few ways how to import files with various extension as well in Excel as in Access environment.

Sub SaveReportAS()

Dim FilePath As Office.FileDialog
Dim Answer As VbMsgBoxResult
Dim FileName As String

Sub SaveMethod1()
'open csv file and read it name
Set FilePath = Application.FileDialog(msoFileDialogSaveAs)

With FilePath
.InitialFileName = ThisWorkbook.Path
.AllowMultiSelect = False
.Title = "Save current worksheet as: "

If .Show = True Then
ActiveWorkbook.Save
ThisWorkbook.SaveCopyAs _
"C:\Users\Agnieszka\Desktop\PROJECT\Offer" & FileRec & ".txt"
End If
End With

'MsgBox ThisWorkbook.Path
End Sub

Sub SaveMethod2()

Dim s As Variant

s = Application.GetSaveAsFilename(InitialFileName:=ThisWorkbook.Path, _
fileFilter:="Excel Files (*.xlsm), *.xlsm", Title:="Save current file: ")

If s <> False Then
ActiveWorkbook.Save
ThisWorkbook.SaveCopyAs _
"C:\Users\Agnieszka\Desktop\PROJECT\Offer" & FileRec & ".txt"
End If

End Sub

Some functions, like special file mark:

Public Function FileRec() As String
FileRec = Format(Now, “_yyyy-mm-dd_hh-mm-ss”) & “_fileauthor_” & Application.UserName
End Function

See whole code here: mImportExport

Second module gonna be about operations we would like to do in Excel after source data implementation.

What I would like to count is:

tax values based on const. rate and sale price.

‘tax value
ActiveSheet.Cells(x, 12).Value = Round(CDbl(ActiveSheet.Cells(x, 10).Value) + _
CDbl(ActiveSheet.Cells(x, 10).Value * 0.23), 1)

and Total Sale Amount including gross price and on_stock quantity.

‘total
ActiveSheet.Cells(x, 13).Value = Round(CDbl(ActiveSheet.Cells(x, 12)) * _
CDbl(ActiveSheet.Cells(x, 9).Value), 1)

Also I would like to have a botton in my project, with which I could restore data before described above operations (tax & total sale).

Sample of these codes you can see by openinig this file with module:

mOperates

Enough for this post. With second part of project we will write macros to enables stats presentations and read data from web sites.

See ya!