Complicated Data Import (1 Viewer)

[HotelierDream]

Hello all,

I could probably spend hours on google finding different pieces of the puzzle to put together but due to past experience I am trying to do this in little code. Here is the deal:

I have 2 databases, one for running functions from another, basically FE and BE. The set is designed to assist with daily reporting for a hotel. Each night the hotels system saves a XML file named [XMLFILENAME.DATE] to a file path on the hotels server (where the DB is), I need to find a way to program a form from the FE of the database to import the file into a table (on the BE) when the user hits a command button on a form in the FE, the DB would also need to choose the file based on the value of a textbox (the date part of the file name).

To summarize:

Person using FE enters value into textbox on a form and hits a command button
The command button uses the value to lookup xml file in folder specified and then uses import template to append table.

Any ideas? I am sure there is a way but I am not sure the way.

 

[theDBguy]

Hi. That doesn't sound complicated at all. Maybe if you ask specific questions about specific steps of the process that you need assistance with, we can give you specific guidance on getting it to work. You should be able to put all the pieces together, once you have them available to you.

Just a thought...

 

[HotelierDream]

Hi. That doesn't sound complicated at all. Maybe if you ask specific questions about specific steps of the process that you need assistance with, we can give you specific guidance on getting it to work. You should be able to put all the pieces together, once you have them available to you.

Just a thought...

Perhaps you can help me figure out whats wrong with this code? I get a file path error due to the variable not working correctly. Also, would this copy the format of the previous xml tables? It is the same file just a different day but I dont know how to set deliminators and such using VBA.

Private Sub Command0_Click()
Dim datasource As String
Dim Dataa As String
Dim ABC As String

Me.DateSet.SetFocus
ABC = Me.DateSet.Text = Replace(Me.DateSet.Text, "/", "")
Dataa = ABC
datasource = "d:\micros\opera\export\opera\rkpcs\xml\mrk" & " " & ABC
Application.ImportXML datasource, acAppendData

End Sub

 

[arnelgp]

what is "mrk", a prefix or a folder?
if folder:

datasource = "d:\micros\opera\export\opera\rkpcs\xml\mrk\" & ABC

 

[CJ_London]

this does not make sense to me

ABC = Me.DateSet.Text = Replace(Me.DateSet.Text, "/", "")

that assigns a boolean value to ABC

Have you tried stepping through your code to make sure you are getting the right values?

 

[HotelierDream]

Mrk is the start of the filed name.

The file name is mrk {date}.xml

 

[HotelierDream]

this does not make sense to me



that assigns a boolean value to ABC

Have you tried stepping through your code to make sure you are getting the right values?

Also, I need the date to be stripped of the / in between numbers. So like 271220 would be the value if 12/27/20 is inserted.

 

[theDBguy]

Also, I need the date to be stripped of the / in between numbers. So like 271220 would be the value if 12/27/20 is inserted.

You could try using the Format() function. For example,

Format(Date(), "ddmmyy")

 

[HotelierDream]

I updated it to this...

Private Sub Command0_Click()
Dim datasource As String
Dim Dataa As String
Dim ABC As String

Me.DateSet.SetFocus
ABC = Format(Me.DateSet.Text, "ddmmyy")
Dataa = ABC
datasource = "d:\micros\opera\export\opera\rkpcs\xml\mrk" & " " & "ABC"
Application.ImportXML datasource, acAppendData

End Sub

It is still giving me a path error because it uses ABC as literally ABC instead of the value of Format(Me.DateSet.Text, "ddmmyy"). Any ideas?

 

[Gasman]

I updated it to this...

Private Sub Command0_Click()
Dim datasource As String
Dim Dataa As String
Dim ABC As String

Me.DateSet.SetFocus
ABC = Format(Me.DateSet.Text, "ddmmyy")
Dataa = ABC
datasource = "d:\micros\opera\export\opera\rkpcs\xml\mrk" & " " & "ABC"
Application.ImportXML datasource, acAppendData

End Sub

It is still giving me a path error because it uses ABC as literally ABC instead of the value of Format(Me.DateSet.Text, "ddmmyy"). Any ideas?

Remove the quotes around ABC

 

[HotelierDream]

When I try that I get this error:

 

[Gasman]

When I try that I get this error:

View attachment 87765

You have to make sure you have a value for ABC ?
Walk through your code with F8. Not sure why you are using the text property and not the default value property?

This is all basic debugging.?

Edit: I would also not use any spaces in the path?

 

[HotelierDream]

You have to make sure you have a value for ABC ?
Walk through your code with F8. Not sure why you are using the text property and not the default value property?

This is all basic debugging.?

So I need to assign a value to ABC that is the value in a text box on my form but in the format of DDMMYY.

So if my form textbox is 12/02/2020 then I need ABC to equal 021220 .

Thats what I am trying to do.

 

[Gasman]

ABC = Format(Me.DateSet, "ddmmyy")

You still have to ensure the control has a value.

 

[HotelierDream]

ABC = Format(Me.DateSet, "ddmmyy")

You still have to ensure the control has a value.

I guess I am asking, how do I give it a value in the formatted way?

 

[Gasman]

I guess I am asking, how do I give it a value in the formatted way?

That code will do it, but the control MUST have a value to be formatted?, so put a date in that form control.

 

[HotelierDream]

That code will do it, but the control MUST have a value to be formatted?, so put a date in that form control.

So this?

ABC = Dateset.value = Format(date,"ddmmyy")

 

[Gasman]

So this?

ABC = Dateset.value = Format(date,"ddmmyy")

No!

Use what has been posted.

 

[bastanu]

Please try:

Private Sub Command0_Click()
Dim datasource As String
Dim sABC As String

sABC = Format(Me.DateSet, "ddmmyy")

datasource = "d:\micros\opera\export\opera\rkpcs\xml\mrk" & sABC & ".xml"
Application.ImportXML datasource, acAppendData

End Sub

You need to add the file extension (xml) to the file name.

Cheers,
Vlad

 

[HotelierDream]

Please try:

Private Sub Command0_Click()
Dim datasource As String
Dim sABC As String

sABC = Format(Me.DateSet, "ddmmyy")

datasource = "d:\micros\opera\export\opera\rkpcs\xml\mrk" & sABC & ".xml"
Application.ImportXML datasource, acAppendData

End Sub

You need to add the file extension (xml) to the file name.

Cheers,
Vlad

I figured it out, new question....

How would I move a file from one drive to another. The original folder will only have one file in it with a random name that will not be consitent therefore I need to move the file in that folder to another and then save it as a specific name, then delete the original file.

Example:

I have a file in folder X:\MLP\data\origin\Like.xml
I need to move the file to C:\OMG\Data\Origin\01202020.xml
Then delete file X:\MLP\data\origin\Like.xml
and the name of file X:\MLP\data\origin\Like.xml changes so I would need it to take the file from the folder.

Note: the folder will only have on file in it.