Open and update Excel Workbook and saveas Question

[Gasman]

Sorry Gasman what do you mean?
I did not say that anyone gave me this code.

Just was trying out.

Ok, you cannot throw a filename into an openrecordset like that. You either need a domain name or a select statement, like the one that follows.

 

[silentwolf]

Hi guys,
many thanks to you all for helping me and giving me good tips and hints!
I will go through those and get back to you!

Really appreciate your help!

Cheers

 

[ebs17]

Consecutive numbering could be added directly to the CSV.
- You read the contents of the CSV into an ADODB recordset. Query about this:

SELECT 0 AS SeqNum, T.*
FROM [Text;DSN=NameSpezification;FMT=Delimited;HDR=yes;IMEX=2;CharacterSet=850;DATABASE=C:\temp\].[datei01.csv]

- Then you do a one-time run through the recordset and number SeqNum. This is much faster than the DCount method mentioned above.
- The third step is to jump to the beginning of the recordset using MoveFirst and use the Getstring method to create a string from the entire contents of the recordset. This string, now with consecutive numbering, is now written back into the CSV or into a copy of it.

 

[silentwolf]

Hi guys,

many thanks for your help! To clearify things to me!
Unfortuantelly the next few days I will not be able to process all your help but will do a soon I can and get back to you!

However I do have just one question which would be in my case the first step what needs to be done.

The bank statements are named all with certain beginnings like "umsaetze-girokonto_IBAN_Day-Mont-Year-Time.csv".
So they include "-" which is not a valid name in Access so if I process those original files I run into problems..

Therefore I like to rename those files all in a certain structure.
"12345_JanToMar_2023.csv" which I get from the last 5 Digits of the IBAN, lowest Month of the file and the Highest Month of the File and the Year of that periode.

Also an validation incase that is not even the right file I included in my case checking the header row. If the header row has all the field names in the correct order and names it is the correct file and can be processed.
That validation I would like to have as some are older files and have been renamed already either manually or via code.
As I like to have it better structured I like to rename all in the same structure and then delete duplicate files and exclude those for my import.

Again all of that was writen in open workbook and check for the header row, check lowest and highest Date and IBAN Number.

As you guys mentioned that there could be an issue of doing it like that I was wondering how it could be done without facing any problems?

Many thanks

Albert

 

[CJ_London]

Use the name command

name oldname as newname

don’t think your new name is very good - the display order will be all over the place. Suggest

2023_01__04

 

[silentwolf]

Hi guys,
@CJ_London
well I would like to include the BankName so to speak so maybe it needs to be like 2023_01_04_12345.csv Would that be ok?

And what do I supose to use like can I somehow open the file to check the dates and soforth as discriped withouth any issue?

@Pat,
well when I have included a file name with "-" then it tells me for the import that it is not a valid file name so to speak not sure if that is the case all the time but I did get this kind of error before.
This was the case when I tried to import that file with the original name straight into access.

But perhabs is also a problem as the file name is in the original name about 60 plus characters.

 

[silentwolf]

Sorry Pat,
I am a little lost right now

You would not have an example of how to get the data out of the csv file as requiered by any chance?

Cheers

 

[ebs17]

FileCopy statement (VBA)

Office VBA reference topic

learn.microsoft.com

Name statement (VBA)

Office VBA reference topic

learn.microsoft.com

You can do this immediately when you first access the file (file selection dialog, loop in the directory).

 

[silentwolf]

Hi Eberhard,

thank you! I will try that many thanks!

Cheers

 

[CJ_London]

well I would like to include the BankName so to speak

I was only showing the date part - include a bank name either before or after (I would go for before)

As far as a data process is concerned, it depends on your situation.

I have one client, they download a csv file from their bank at the end of the month and as and when required. The process there is

1. download file from bank
2. using FSO, they select the file from the download folder
3. the app then copies the file from the download folder to another 'import' folder with a standard name e.g. 'BankStatement.csv', replacing whatever
4. the app then (using the sql method I outlined above) imports the data, ignoring any records already imported (when matched on date, detail, amount and balance - very slight risk here of duplicates, but in 12 years with this client, never been a problem)
5. once successfully imported, the file is then copied to an archive folder

Another client has 5 bank accounts which are auto downloaded daily. For them the process is similar except steps 1 and 2 are replaced with process to loop through the download folder, typically identifying a file by bank name and date then continuing from step 3.

There is a further refinement, each bank can have a different format - additional columns, different column names, a different column order, different currency and date formats, identity rows at the top for example. So the sql a) has to modify the criteria to exclude the identity rows, b) sort out the names and order and c) do some data type conversion.

This is handled in a meta table to construct the sql in VBA as required for each bank. I also set HDR=No which gets around the problem of illegal field names. If you do this then the columns are automatically named F1, F2 etc. So for one Bank I would assign F1 to transactionDate, for another it might be F2. The meta table looks like this

PK....BankFK....destFld.....impFld....conversion
1............1...........tranDate.....F1
2............1...........detail............F6
3............2...........trandate......F2
4............2............detail...........F5

 

[silentwolf]

Thanks CJ,
I will look into that..

Cheers!

 

[silentwolf]

Hi guys,

I am back and trying to work out CSV Import.
First question still remains why I can not import a File with a name like following:
"umsaetze_girokonto_AT221111011010110011_EUR_2021_10_13_08_17_32.csv"

If I run this Code below In the Imidate window "Call ImportCSVFile("umsaetze_girokonto_AT221111011010110011_EUR_2021_10_13_08_17_32.csv")
I get a runtime Error of 3011
If the filename is "TestCSV.csv") it works.
Therefore I thought of renaming the file as mentioned in previous posts.

Public Sub ImportCSVFile(FileName As String)
    
    DoCmd.TransferText acImportDelim, "Volksbank Import Spezification", "VBImport", _
        FileName, True, , 1252
        
End Sub

Also for the sake of knowing which CSV File it is I wanted to rename it but can't seam to find a way of doing it as jet.

Cheers

Albert

 

[Gasman]

And runtime error 3011 is?
You do need the FULL path for the file?

 

[silentwolf]

Hi Gasman,
as soon as I use a filename as mentioned I get the same error.

"C:\Users\Albert\Documents\AJL\CSV_Daten\Test\umsaetze_girokonto_AT221111011010110011_EUR_2021_10_13_08_17_32.csv"

Error

"C:\Users\Albert\Documents\AJL\CSV_Daten\Test\01-03_2016.csv"

Works

So what could the issue be? The error says can't find the file but the file exists is correct writen but it always runs into the same Error if a file is with similar name.

 

[Gasman]

I would calculate the length of the name and shorten it in increments until it works.
Their might be a limit to full pathname.
Put it higher up the folder structure first with same name and try that.

 

[silentwolf]

I would calculate the length of the name and shorten it in increments until it works.
Their might be a limit to full pathname.
Put it higher up the folder structure first with same name and try that.

Thanks,
unfortunatelly same issue when I put it into a higher folder .( to reduce the path and file name

 

[ebs17]

"C:\Users\Albert\Documents\AJL\CSV_Daten\Test\umsaetze_girokonto_AT221111011010110011_EUR_2021_10_13_08_17_32.csv"

Are you able to capture the full path shown via code?
At this moment you would be able to read the IBAN and timestamp from the file name (you would certainly need such information for a complete import including documentation) and then immediately provide the file with a usable path and name using Name/FileCopy and thus to start the import.

 

[silentwolf]

Hi Eberhard,

Are you able to capture the full path shown via code?

Not really sure what you mean with that sorry?

At present I get the fullpath and filename with this procedure

Private Sub btnBrowse_Click()
    Dim diag As Office.FileDialog
    Dim item As Variant
    
    Set diag = Application.FileDialog(msoFileDialogFilePicker)
    
    With diag
        .AllowMultiSelect = False
        .Title = "Excel oder CSV-Datei auswählen"
        .Filters.Clear
        .Filters.Add "Excel Spreadsheets", "*.xls,*.xlsx,*.csv"
        .FilterIndex = 2
        
        .InitialFileName = p_cstrCSVTestDir 'Verzeichnis Konstante noch ändern
    
        If .Show And .SelectedItems.Count > 0 Then
            Me!txtFilePath.Value = .SelectedItems.item(1)
        End If
    End With
    
    Set diag = Nothing

End Sub

I would not mind beeing able to rename the file according to what the file content is but I am not able to get any information on the net via text file.

Only found this function on MrExcel.com but forgot to save the link
To get or retrieve the first line of the text file.

Function GetFirstLine(MyFile As String) As String
    If Dir(MyFile) <> "" Then
        Open MyFile For Input As #1
            Line Input #1, GetFirstLine
        Close
    End If
End Function

Sub TestValidationFirstLine()
    Dim strFullName As String
    Dim strFirstLine As String
    
    strFullName = "C:\Users\Albert\Documents\AJL\CSV_Daten\Test\11222_10_12NurTest.csv"
    strFirstLine = GetFirstLine(strFullName)
    
    If strFirstLine = "IBAN;Auszugsnummer;Buchungsdatum;Valutadatum;Umsatzzeit;Zahlungsreferenz;Waehrung;Betrag;Buchungstext;Umsatztext" Then
        Debug.Print strFirstLine
    Else
        Debug.Print "False Data"
    End If

End Sub

This code above is just for testing purpose to check if the first line of the textfile is correct and can proceed if it is correct.

But can't find anything as yet to get data from csv out from a certain column so I am able to get the datefrom and dateTo of that file.

I guess I just have to rename each file manually and save it to a new location to be able to savely import it to access.

At this moment you would be able to read the ISBN and timestamp from the file name

ISBN oop not sure .. never used it or not sure what or where I get that information from the file?

 

[spaLOGICng]

Hi again guys!
it hast been a while and I hope all of you are very well.
However now I am back in the game and like to work on following where I like to get some advice please.

I have a form where I can either open an CSV File in Excel.. for viewing purpose.
Update and Save the file into new location ("in progress "
What I like to know is is it possible to open the file via code and update that file save it to new location but keep the opend file?

So if I browse for the file and I need to update and save that file into a new location after it has been updated will the original file be lost?
If yes then I guess I need to create a copy of that file first save it to a new location and after that I will have to modify that new file to suit?

Or can I just open one file update it, saveas "newName.csv" to new location and let the original untoucht?

Many thanks for any advice of how you guys going about that in your projects.

Cheers

Albert

Yes.

In VBA you can use the SaveCopyAs method under the Workbook Object. Yous simply provide the the Filename that will include the folder path where you want to save it to.

This saves the Workbook/CSV as a duplicate. The current opened version is not affected in any way.

 

[ebs17]

ISBN was a misnomer that I quickly corrected.
AT221111011010110011 - I think this is the IBAN (bank and account number).

The other thing you want to do now is unclear to me: Above, an import via SQL/Recordset or TransferText or TransferSpreadsheet was possible. But something like this requires standard structures within the CSV.

Now you want to look at the first line of content. So the structure seems to be anything but standardized; the name of the file does not guarantee expected and immediately usable content.

You still have no overview or concept.