By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 1,221 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Uploading Information

P: 34
Previously i uploaded information into my database as xls via vba code in access e.g:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tbl_MinWage", "R:\HR\Minimum Wage\Exports from ADP\NMW CH " & Text33, True.

By using this method i would be able to save a excel spreadsheet in a folder and add a date on the end of the document e.g. NMW CH 081007.xls

Then in my access form i would have a text box i.e. Text33 and just add ion the date e.g. 081007 and click a button. The vba code would then select the correct file e.g. NMW CH 081007.xls

I an now trying the same thing in txt format but it does not work. I get an error. e.g.

DoCmd.TransferText acImportDelim, "tbl_01_BKUK_Active&Withdrawn_ Import Specification", "tbl_01_BKUK_Active&Withdrawn", "R:\HR\HR_System_Reports_Folder\ADP_Downloads\HC_R SCUK" & Text33, True.


Does anyone know why or how I can get around this?
Oct 10 '07 #1
Share this Question
Share on Google+
8 Replies


nico5038
Expert 2.5K+
P: 3,072
I miss the ".txt" extension in the text import.
Are you sure that the "&" in the tablename and the filename causes no trouble?
What is the message you get ?

Nic;o)
Oct 11 '07 #2

P: 34
Thaks for reply.

I get error message runtime 31519.

any ideas? Do you have example code that works for You at all?

I can get the following code to workto upload a txt file:-

DoCmd.TransferText acImportDelim, "tbl_01_BKUK_ActiveWithdrawn_ Import Specification", "tbl_01_BKUK_ActiveWithdrawn", "R:\HR\HR_System_Reports_Folder\ADP_Downloads\HC_R SCUK.txt", False, ""

However I cannot tailor it so i can get a specify a dated txt e.g. HC_RSCUK_121007.

Cheers
Oct 12 '07 #3

nico5038
Expert 2.5K+
P: 3,072
Thaks for reply.

I get error message runtime 31519.

any ideas? Do you have example code that works for You at all?

I can get the following code to workto upload a txt file:-

DoCmd.TransferText acImportDelim, "tbl_01_BKUK_ActiveWithdrawn_ Import Specification", "tbl_01_BKUK_ActiveWithdrawn", "R:\HR\HR_System_Reports_Folder\ADP_Downloads\HC_R SCUK.txt", False, ""

However I cannot tailor it so i can get a specify a dated txt e.g. HC_RSCUK_121007.

Cheers
Found several hits on this error, all indicating that it's the ".txt" that needs to be present in the filename.
When you get a file named "HC_RSCUK_121007", then first use a FileCopy command to create a copy with the ".txt" to the name before processing the TransferText.

Nic;o)
Oct 12 '07 #4

P: 34
Nico,

Thanks you your response but I am still unsure with regards to this.

The second part of the file name needs to be dynamic i.e. HC_RSCUK_121007.txt or HC_RSCUK_171007.txt depending on what file date I need???

Apologies in advance if Ihave misunderstood.

Regards,

Forrest Gump
Oct 24 '07 #5

nico5038
Expert 2.5K+
P: 3,072
Your present desription indicates an import of an .xls
as your cammand looks like:
"R:\HR\Minimum Wage\Exports from ADP\NMW CH " & Text33
the field Text33 should also hold the file extension: ".xls"
otherwise you need to change the string into:
"R:\HR\Minimum Wage\Exports from ADP\NMW CH " & Text33 & ".xls"

Getting the idea ?

Nic;o)
Oct 24 '07 #6

P: 34
I can get this to work as a .xls it is when it is a .txt I am having a problem.

Regards,

Forrest


Your present desription indicates an import of an .xls
as your cammand looks like:
"R:\HR\Minimum Wage\Exports from ADP\NMW CH " & Text33
the field Text33 should also hold the file extension: ".xls"
otherwise you need to change the string into:
"R:\HR\Minimum Wage\Exports from ADP\NMW CH " & Text33 & ".xls"

Getting the idea ?

Nic;o)
Oct 24 '07 #7

nico5038
Expert 2.5K+
P: 3,072
When the trailing ".txt" is present it should work with the TransferText command, what's the error message?

Nic;o)
Oct 24 '07 #8

P: 34
Brilliant!!!

Got it working!!

Thaks very much for your help on this.

Regards,

Forrest

When the trailing ".txt" is present it should work with the TransferText command, what's the error message?

Nic;o)
Oct 24 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.