Open and update Excel Workbook and saveas Question

[silentwolf]

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

 

[Minty]

The safest way would be to

1. Open the existing File
2. Immediately SaveAs the new version
3. Make you updates
4. Save

That way any issues/crashes won't affect your existing file.
SaveAs as the last command is fraught with potential issues.

 

[silentwolf]

The safest way would be to

1. Open the existing File
2. Immediately SaveAs the new version
3. Make you updates
4. Save

That way any issues/crashes won't affect your existing file.
SaveAs as the last command is fraught with potential issues.

Hi Minty,
many thanks for your help!
Question:
So if I open the existing file with "oldName.csv" and saveas will then I work with the new file automatically so to say?

Cheers

 

[Minty]

Yes - assuming you are setting an Object to the Excel Application, as soon as you SaveAs, the Object will be pointing at the "SavedAs" file.

 

[silentwolf]

Ok thank you Minty!
I will try that!

Cheers

Albert

 

[Pat Hartman]

Doesn't Excel have the concept of templates where when you open the template it automatically copies the file and you work in the copy? I know that's how the .dot file works with Word.

We're answering a question but we have no context? Maybe the saveas code must be in the spreadsheet and run when the spreadsheet is opened?

 

[CJ_London]

Just be aware that opening a csv file in excel and the saving as a .csv can change underlying field types (e.g. numbers and dates can get saved as text and visa versa). Might be OK if then only opened in excel but opening as a text file for importing (for example) may no longer work. Not always, but it's happened enough with me to simply not go down that route.

 

[silentwolf]

Doesn't Excel have the concept of templates where when you open the template it automatically copies the file and you work in the copy? I know that's how the .dot file works with Word.

We're answering a question but we have no context? Maybe the saveas code must be in the spreadsheet and run when the spreadsheet is opened?

Hi Pat,
Hi CJ,

thanks to both of you for your replies!

Doesn't Excel have the concept of templates where when you open the template it automatically copies the file and you work in the copy?

Unfortualtelly I am not to sure about that either .(
But it is a Bank Statement I am working on and in the end it gets importet to Access.

So some issues or perhabs where I am not to sure what the best approach is.
1. I like to be able to view the Data as some of the Files have already been saved as different names for example. So if I open it from my form it opens in Excel. Perhaps I should open it as plain text file? If so how to? It always opens in Excel.

2. If the File is what I am looking for I like to saveas NewFileName and in new Location. Well that I guess should be ok to get done.

3. I like to keep the original CSV File for reference purpose

4. In Access I have been able to import CSV Files into an Access table. Thanks to Mr.Stiefel and his Videos on Codekabinett.com

Cj London:

Just be aware that opening a csv file in excel and the saving as a .csv can change underlying field types (e.g. numbers and dates can get saved as text and visa versa)

5. The only issue sometimes occurs with that is that in one "Column" are mostly numbers but some text and there where times when those filled with text did not get importet. (I guess it is now sorted but as I created a import specification as suggested and I guess it worked ok) need more testing on that though.

For that reason I created a function that could update that mentioned field as a text. But I guess it is also enough when I have the Import Table in Access set to a text field and therefore should not really need to update this field before importing right?

I got the bits and peaces together but still not to sure about the best approach of beeing able to handle those requierments within one "project".

Hope that is now a bit more clear and some of you guys could give me some suggestion?

Many thanks in advance

Albert

 

[Pat Hartman]

It always opens in Excel.

That is because when Excel installs, it conveniently adds .csv as one if its own file types. So, if you double click on a .csv, it opens by Excel rather than by NotePad.

The CORRECT way to open a .csv using Excel is to open Excel and then go to the data tab and "import" the .csv file. Excel has a tendency to break files that have columns that contain long number such as credit card numbers which tend to be 16 characters. Excel displays they using scientific notation. And if you are not careful, the data could be saved that way and so you lose most of the number.

My bank statements come as .pdf's and I have to convert them to Excel before I can import them into Access. We do that by running the conversion software. This converts all .pdf files in the folder. Access then reads the Excel files and tries to import them. If successful, the Excel file is placed in the processed folder and the .pdf is placed in the processed .pdf folder. Because they are bank statements, the code ensures that they are imported in sequence. i.e. September can't be imported if the file is not empty and August wasn't already imported. All the bank statements go into the same table with the accountID included in the appended record. The import is logged in a separate table that includes the file name and date imported as well as a batchID. The batchID is also included as a column for each record appended from the linked excel file. That makes the whole batch easy to delete if there is a problem and it has to be reimported.

If your statements come as csv, I would simply lock all the files and not allow them to be updated. I would never open them in Excel before I processed them. It is too dangerous. You can solve the double-click problem on your computer and connect .csv to NotePad rather than Excel. It is far safer. Then, use the method I described above if you want to view the file with Excel.

 

[silentwolf]

That is because when Excel installs, it conveniently adds .csv as one if its own file types. So, if you double click on a .csv, it opens by Excel rather than by NotePad.

The CORRECT way to open a .csv using Excel is to open Excel and then go to the data tab and "import" the .csv file. Excel has a tendency to break files that have columns that contain long number such as credit card numbers which tend to be 16 characters. Excel displays they using scientific notation. And if you are not careful, the data could be saved that way and so you lose most of the number.

My bank statements come as .pdf's and I have to convert them to Excel before I can import them into Access. We do that by running the conversion software. This converts all .pdf files in the folder. Access then reads the Excel files and tries to import them. If successful, the Excel file is placed in the processed folder and the .pdf is placed in the processed .pdf folder. Because they are bank statements, the code ensures that they are imported in sequence. i.e. September can't be imported if the file is not empty and August wasn't already imported. All the bank statements go into the same table with the accountID included in the appended record. The import is logged in a separate table that includes the file name and date imported as well as a batchID. The batchID is also included as a column for each record appended from the linked excel file. That makes the whole batch easy to delete if there is a problem and it has to be reimported.

If your statements come as csv, I would simply lock all the files and not allow them to be updated. I would never open them in Excel before I processed them. It is too dangerous. You can solve the double-click problem on your computer and connect .csv to NotePad rather than Excel. It is far safer. Then, use the method I described above if you want to view the file with Excel.

Hi Pat,
thanks for your answer.

The way I have done it in the past was either from Excel or from Access using the Excel Libary.
Most of the was retrieving information about that file what Bankaccount, FirstDate to LastDate and created a Filename like:
124514_JanToDec_2020.csv" and save that file to a new location.
Updateing some of the information was at the beginning also included with that code.
Now I import the "non updated" File into Access as mentioned and do with queries and Code what need to be changed there.

In the past I had no issues after getting it all sorted bit by bit but I understand when you are saying that this is not a save way of doing it!

So is there any save way of doing it automatically? At least retrieving the Header Information and create a Filename and saveas without fearing that it might get corrupted at any point?

Cheers

 

[ebs17]

save way

A CSV is a text file. If you want to work with Access anyway, you don't need any help with Excel.

If you can import the CSV sensibly (some CSVs are just called that, but have a completely different structure than the standard text table), you can also link them as a table with the same specification and thus view the content without any problems.

 

[silentwolf]

A CSV is a text file. If you want to work with Access anyway, you don't need any help with Excel.

If you can import the CSV sensibly (some CSVs are just called that, but have a completely different structure than the standard text table), you can also link them as a table with the same specification and thus view the content without any problems.

Hi Eberhard,
thanks for your reply!

Ok I understand.. will need to look into that a bit more.
But thanks for pointing that out to me!

Cheers
Albert

 

[silentwolf]

Hi again,

not sure if I suppose to stay in this threat or not but I am trying to keep it in here.
I guess if it is wrong you well tell me )

I was trying now to use ADODB to refer to my folder and one TestCSV File

below is what I got so far.

But I am not sure how to get that into the my Database and in the table for that matter.

I have been trying to find a solution but was not able to.

Sub GetDataFromCSVFiles()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Set cn = New ADODB.Connection
    
    cn.ConnectionString = _
    "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
    "Dbq=C:\Users\Albert\Documents\AJL\CSV_Daten\Test\MyTestFiles\;" & _
    "Extensions=asc,csv,tab,txt;"
    
    cn.Open
    
    Do While cn.State <> 1
        DoEvents
        If cn.State = 0 Then Exit Sub
    Loop
    
    Set rs = New ADODB.Recordset
    
    rs.ActiveConnection = cn
    rs.Source = "SELECT * FROM [TestDaten.csv]"
    rs.Open
    
    If Not rs.EOF Then rs.MoveFirst
    
    Debug.Print rs.RecordCount
    
    Do Until rs.EOF
        Debug.Print rs
        rs.MoveNext

    Loop
    
    
    rs.Close
    cn.Close

End Sub

When I step through the code and hit the debug.print rs.RecordCount I get -1 after going further I get an Error on Debug.print rs
Runtime Error 13

I am just not really to familiar with that so maybe someone could please give me a help to be able to write this code correctly to
import that recordset into my current db "IMPORT" table


Would very much appreciate it!

Many thanks

Albert

 

[CJ_London]

have your tried using DAO?

dim rs as DAO.recordset
dim fpath as string
dim fname as string

fpath="C:\Users\Albert\Documents\AJL\CSV_Daten\Test\MyTestFiles"
fname="TestDaten.csv"
set rs =currentdb.openrecordset("SELECT * FROM (SELECT * FROM  [TEXT;DATABASE=" & fpath & ";HDR=Yes]." & fname & ")  AS txt")
rs.movelast
debug.print rs.recordcount

I alias the csv file so you can then see it in the query grid or reference a field if there are other things you want to do with it such as modify data, append or update another table

further I get an Error on Debug.print rs

you need to specify one or more fields

debug.print rs.fields(0)

or

debug.print rs!fieldname

to insert into your import table change this line assuming you are just importing all records and the import table has identical fields and no extra such as PK or timestamp

set rs =currentdb.openrecordset("SELECT * FROM (SELECT * FROM  [TEXT;DATABASE=" & fpath & ";HDR=Yes]." & fname & ")  AS txt")

to

currentdb.execute("INSERT INTO import SELECT * FROM (SELECT * FROM  [TEXT;DATABASE=" & fpath & ";HDR=Yes]." & fname & ")  AS txt")

if it does have a PK or timestamp or different field names use

currentdb.execute("INSERT INTO import (fld1, fld2) SELECT fld1, fld2 FROM (SELECT * FROM  [TEXT;DATABASE=" & fpath & ";HDR=Yes]." & fname & ")  AS txt")

change fld1, fld2 to the actual names of your field

EDIT:
You can use a left join of the txt file to your import table to avoid importing data already imported and/or to update existing records

You can also do prevalidation of the data before importing, giving the user the choice (if you want to give them the choice) of importing all data including errors, importing the valid records only or aborting the import. And of course obtain a list of the failed records.

For prevalidation, ideally you need a meta table which contains details of the file (nature of the dataset), field name in the file, the field name in the destination table, the data type required and any other validation rules such as a number or date range, length of text etc. If not you can reference the destination tabledef for that information (certainly datatype)

 

[silentwolf]

have your tried using DAO?

dim rs as DAO.recordset
dim fpath as string
dim fname as string

fpath="C:\Users\Albert\Documents\AJL\CSV_Daten\Test\MyTestFiles"
fname="TestDaten.csv"
rs.openrecordset("SELECT * FROM (SELECT * FROM  [TEXT;DATABASE=" & fpath & ";HDR=Yes]." & fname & ")  AS txt")
rs.movelast
debug.print rs.recordcount

I alias the csv file so you can then see it in the query grid or reference a field if there are other things you want to do with it such as modify data, append or update another table


you need to specify one or more fields

debug.print rs.fields(0)

or

debug.print rs!fieldname

to insert into your import table change this line assuming you are just importing all records and the import table has identical fields and no extra such as PK or timestamp

rs.openrecordset("SELECT * FROM (SELECT * FROM  [TEXT;DATABASE=" & fpath & ";HDR=Yes]." & fname & ")  AS txt")

to

currentdb.execute("INSERT INTO import SELECT * FROM (SELECT * FROM  [TEXT;DATABASE=" & fpath & ";HDR=Yes]." & fname & ")  AS txt")

if it does have a PK or timestamp or different field names use

currentdb.execute("INSERT INTO import (fld1, fld2) SELECT fld1, fld2 FROM (SELECT * FROM  [TEXT;DATABASE=" & fpath & ";HDR=Yes]." & fname & ")  AS txt")

change fld1, fld2 to the actual names of your field

EDIT:
You can use a left join of the txt file to your import table to avoid importing data already imported and/or to update existing records

You can also do prevalidation of the data before importing, giving the user the choice (if you want to give them the choice) of importing all data including errors, importing the valid records only or aborting the import. And of course obtain a list of the failed records.

For prevalidation, ideally you need a meta table which contains details of the file (nature of the dataset), field name in the file, the field name in the destination table, the data type required and any other validation rules such as a number or date range, length of text etc. If not you can reference the destination tabledef for that information (certainly datatype)

have your tried using DAO?

No I havent.. (
But I am trying right now but got already an issue..

Currently I got this below..

Sub TestDAO()
    Dim rs As DAO.Recordset
    Dim fpath As String
    Dim fname As String
    
    fpath = "C:\Users\Albert\Documents\AJL\CSV_Daten\Test\MyTestFiles"
    fname = "TestDaten.csv"
    
    Set rs = CurrentDb.OpenRecordset("IMPORT", dbOpenDynaset)
    
    rs.OpenRecordset ("SELECT * FROM (SELECT * FROM  [TEXT;DATABASE=" & fpath & ";HDR=Yes]." & fname & ")  AS txt")

    rs.MoveLast

    Debug.Print rs.RecordCount
End Sub

How to I have to set this object variable rs?
Does it need to be the Database or the CSV Folder?

Above I tried with the Database but running into an error Runtime Error 3421 Datatyp Convertion Error or something down those lines..

if I try this code

Sub TestDAO()
    Dim rs As DAO.Recordset
    Dim fpath As String
    Dim fname As String
    
    fpath = "C:\Users\Albert\Documents\AJL\CSV_Daten\Test\MyTestFiles"
    fname = "TestDaten.csv"
    
    Set rs = CurrentDb.OpenRecordset(fpath, dbOpenDynaset)
    
    rs.OpenRecordset ("SELECT * FROM (SELECT * FROM  [TEXT;DATABASE=" & fpath & ";HDR=Yes]." & fname & ")  AS txt")

    rs.MoveLast

    Debug.Print rs.RecordCount
End Sub

I get the runtime Error 3078 The microsof Access Databasemodule cant find "C:\Users\Albert\Documents\AJL\CSV_Daten\Test\MyTestFiles

Maybe someone can point me to the right direction on that?

Cheers

 

[CJ_London]

my bad - that's the problem with writing air code - have edited my original post

 

[silentwolf]

my bad - that's the problem with writing air code - have edited my original post

Hi CJ,
many thanks!!
Well now I am getting there I will try to sort it further and get back to you!

Cheers

 

[Gasman]

You were not given this code?

fpath = "C:\Users\Albert\Documents\AJL\CSV_Daten\Test\MyTestFiles"
    fname = "TestDaten.csv"
    
    Set rs = CurrentDb.OpenRecordset(fpath, dbOpenDynaset)

 

[silentwolf]

Hi CJ,

still some issues with insert into statement

Sub testNewInsert()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fpath As String
    Dim fname As String
    
    fpath = "C:\Users\Albert\Documents\AJL\CSV_Daten\Test\MyTestFiles"
    fname = "TestDaten.csv"
    
    Set db = CurrentDb
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM (SELECT * FROM  [TEXT;DATABASE=" & fpath & ";HDR=Yes]." & fname & ")  AS txt")
        
    rs.MoveLast
    
    db.Execute ("INSERT INTO import SELECT * FROM (SELECT * FROM  [TEXT;DATABASE=" & fpath & ";HDR=Yes]." & fname & ")  AS txt")

End Sub

This is what I have tried..
Now I get an runtime Error 3127
The ISERT INTO Statement has following unknown fieldnames
IBAN; Auszusnummer; and so on

I do have in my IMPORT table all those fields and also those fields are given in the csv file.

So I guess there must be some other issue I cant spot?

 

[silentwolf]

You were not given this code?

fpath = "C:\Users\Albert\Documents\AJL\CSV_Daten\Test\MyTestFiles"
    fname = "TestDaten.csv"
   
    Set rs = CurrentDb.OpenRecordset(fpath, dbOpenDynaset)

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

Just was trying out.