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!