TransferSpreadsheet (Only Specific Columns)

[Sevn]

Hello All,

I am average when it comes to VBA, and I am mostly self taught.

What I need to do is automate a daily import process from daily DB exports.

Here is what currently happens:

• I receive daily exports of several different DB grids (3 total). These exports are in CSV format, with every field from the main DB grid.
• I use the import wizard to import the fields that i need (Only need about 20% of the ~156 columns contained in each CSV).
• I would like to import these using VBA, but can't seem to find any sample code for skipping unwanted columns. I have found some examples of Range importing, but the columns I need are scattered throughout the worksheet (A:C, F:R, AB:AC, etc.).

The only option I can think of is to import everything, and remove unwanted columns. Although this seems like a lot of unnecessary processing for the few fields that I need.

If anyone has any suggestions, please let me know.

 

[DCrake]

Simple Software Solutions

If the layout is static for the spreadsheets then I suggest you simply link the spreadsheets to your mdb and create a query that selects the desired fields for your append. This will save you having to import uneccessary data. As the contents of the spreadsheets change the changes are reflected in the links as and when they are made so you will always be up to date.

CodeMaster:

 

[Sevn]

Daily CSV Exports

Thanks DCrake,

That would work, but the exports work like this:

• For a given date (05/01/2008), at midnight the DB will export all activity for the current month. Friday morning I will have a CSV that has all data for the month of May. The export is programatically named 20080501-Detail Grid.csv (for example).
• The next morning I will get a new CSV named 20080502-Detail Grid.csv. This export will have all data for the month of May (5/1-5/2).
• So with that said, the daily exports will really be overwriting the previous days data, and adding the new days data.
• My plan was to query the current month's data, remove it, and import the new worksheet daily. So on 20080515, I would query all data related to 200805 (May), remove it, and import the new CSV. The CSV provided to me on 20080515 will have all data from 20080501-20080514.

 

[Sevn]

Dynamic Linking Of Workbook

In regards to your linking suggestion...

Is it possible to create a dynamic link to the workbooks? Meaning that it will import the workbook that applies to that day. The example below is for importing, but i assume there is a way to link in the same manner.

In my head:
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=7, _
TableName:="Detail_Grid_Data", _
FileName:=\\blah.com\Share\blah\BlahDownloads\" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2) & "-Detail Grid.csv", _
HasFieldNames:=False

Any help would be appreciated.

 

[DCrake]

Simple Software Solutions

Firstly create a macro to import a specifically named spreadsheet.
Next convert the macro to vba
In a module create a function that handles the naming conventions as per your previous thread.

DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=7, _
TableName:="Detail_Grid_Data", _
FileName:=\\blah.com\Share\blah\BlahDownloads\" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2) & "-Detail Grid.csv", _
HasFieldNames:=False

Then on the onclick of a button or within a new macro call the function.

Command Button:

    Call ImportMyFile

Macro:

    RunCommand ImoportMyFile

Function ImportMyFile()

Dim TodayFile as string

TodayFile = "\\blah.com\Share\blah\BlahDownloads\" & Format(Now(),"yyyymmdd") & "-Detail Grid.csv"

If Dir(TodayFile) <> "" Then
    DoCmd.SetWarnings False
    DoCmd.TransferSpreadsheet  acImport,7,"Detail_Grid_Data",TodayFile,False
    DoCmdSetWarnings True
Else
    MsgBox "Cannot find source file to import." & vbcrlf & vbcrlf & "Please check that the source file has been generated then retry.",vbExclamation,vbOkOnly,"Import Failed"
End If

End Function

Summary:
This will work if you are importing the file for today's date only. To enhance this feature you could pass a date to the function that will act as the parameter for building the file name. Suppose you are off one day and miss a previous days import this added functionality will enable you to pass a date to the function and it will respond to that particular date.

To do that change the function to read:

Function ImportMyFile(AnyDate As Date)


....
TodayFile = "\\blah.com\Share\blah\BlahDownloads\" & Format(AnyDate,"yyyymmdd") & "-Detail Grid.csv"
....

End Function


Regards
David

 

[Sevn]

Thanks for the info. This project has been changed a little, as to I will be getting just 1 export with everything I need.

Can your logic be applied to an On Open Timer event of the DB? Let's say a user logs in to the DB after 7AM, and the DB triggers your code to process the import.

I have seen some timer code, but am unsure how it will work since the DB may not be open @ my set time (7AM).

Thanks,

 

[DCrake]

Simple Software Solutions

If you have a log table that records when the last tlme an import was performed and on what date you could simply use the on form open of your first splash screen.

Place a timer interval on the splash screen, say 1 second, (1000) this will display the form on screen. Then on the OnTimer Event set the TimerInterval to 0 so it won't run again. Then check the table for the date and time stamp of the last import. It it needs to be done then import it or ignore it. Don't forget when you import it to update the log table with the date time stamp.

David

 

[Sevn]

I think I understood your explanation as follows;

1. Create Log table for importing.
2. Create splash screen with 1 sec timer.
3. During 1 sec, log current date, check date against logged dates in Log table.
4. If date exists, stop macro; otherwise, import current date data.

I'm not sure that will work. The DB won't be opened on Saturdays or Sundays, but the data will need to be imported. Therefore; on Mondays I would need to import Friday-Sunday's data. I will check with my IT team, and see if I can get the weekends exported on Monday to make this easier.